topic Re: Calculate formula not Totaling in DAX Commands and Tips

Calculate formula not Totaling

mcornfield — Fri, 12 Mar 2021 18:07:43 GMT

Can anyone help me with this formula?

Calculate formula not totaling

Re: Calculate formula not Totaling

themistoklis — Fri, 12 Mar 2021 21:25:08 GMT

try this formula

Rev SWLY2 = var _weekid52 = SELECTEDVALUE(DateTable[WeekID]) - 52 return CALCULATE(SUM('Fact Table'[Revenue Net]), FILTER(ALL(DateTable), DateTable[WeekID] =_weekid52 ))

Re: Calculate formula not Totaling

AntrikshSharma — Sat, 13 Mar 2021 02:46:59 GMT

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

Re: Calculate formula not Totaling

mcornfield — Mon, 15 Mar 2021 14:26:08 GMT

This gave me a total but the the wrong subtotals

Re: Calculate formula not Totaling

mcornfield — Mon, 15 Mar 2021 14:27:13 GMT

This solution did not total either for some reason

Re: Calculate formula not Totaling

jameszhang0805 — Mon, 15 Mar 2021 15:04:18 GMT

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 ) )

Re: Calculate formula not Totaling

Anonymous — Mon, 15 Mar 2021 18:42:52 GMT

Hi mcornfield , you need use SUMX and Summarize in another measure.

try this

MeasureTotal = SUMX (

ADDCOLUMNS (

SUMMARIZE ( 'Table', 'Table'[Date] ),

"Result", [First Measure without Total]

[Result]

)

Re: Calculate formula not Totaling

AntrikshSharma — Tue, 16 Mar 2021 04:51:20 GMT

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 🙂