Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Can anyone help me with this formula?
Calculate formula not totaling
try this code :
Rev SWLY2 =
VAR _weekid52 =
MAX ( DateTable[WeekID] ) - 52
RETURN
SUMX (
VALUES ( DateTable[WeekEnding] ),
CALCULATE (
SUM ( 'Fact Table'[Revenue Net] ),
FILTER ( ALL ( DateTable ), DateTable[WeekID] = _weekid52 )
)
@mcornfield SELECTEDVALUE only return a single value and at the grand total there are no filters so you have to use MAX or MIN like this:
Rev =
VAR MaxDate =
CALCULATE (
MAX ( DateTable[WeekID] ),
FILTER ( VALUES ( Dates[Calendar Year Number] ), [Revenue Net] <> 0 )
)
VAR Result =
CALCULATE (
[Revenue Net],
DateTable[WeekID] = MaxDate - 10,
REMOVEFILTERS ( DateTable )
)
RETURN
Result
This gave me a total but the the wrong subtotals
@mcornfield hmmm actually, I made a guess based on @themistoklis code, if none of the solutions on this page works then you have to share power bi file, also make sure going forward you provided data upfront so that it save everyone's time 🙂
try this formula
Rev SWLY2 =
var _weekid52 = SELECTEDVALUE(DateTable[WeekID]) - 52
return
CALCULATE(SUM('Fact Table'[Revenue Net]), FILTER(ALL(DateTable), DateTable[WeekID] =_weekid52 ))
This solution did not total either for some reason
Hi @mcornfield , you need use SUMX and Summarize in another measure.
try this
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |