topic Grand Total returning 0 in DAX Commands and Tips

Grand Total returning 0

jasemilly — Fri, 17 May 2024 11:33:58 GMT

Hi I am creating a measure that shows a reimbursement per order depending on how late it was at the row level it works fine but The grand total shows, I am using the has one value function to check and use the summarised measure I have created for a total my table looks like this

I have changed from If to Switch.

KEY BILL Arrival Minutes Late Reimbursement

1 10.50 5 12.20

2 9 2 1.50

Reimbursement = var _mins = SELECTEDVALUE(Journeys[Arrival Minutes Late]) var _perc = SWITCH( TRUE(), _mins>= 21, 1, _mins >= 16, 0.8, _mins >= 11, 0.6, _mins >= 6, 0.4, _mins >= 1, 0.2 ) Var JourneyTotal = Sumx(Journeys,Journeys[Bill] * _perc) Var GrandTotal = SUMMARIZE(Journeys,Journeys[Key],Journeys[Arrival Minutes Late], "Gtotal", JourneyTotal) Return IF( HASONEVALUE(Journeys[Key]), JourneyTotal, SUMX(GrandTotal,[Gtotal] ) )

Thanks for all help

Re: Grand Total returning 0

mark_endicott — Fri, 17 May 2024 11:53:38 GMT

jasemilly - The _perc variable does not sit within the rows of the table, therefore it cannot calculate at the total level.

I would suggest adding it into your data as a column via Power Query or a Calculated Column.

The sumx for JourneyTotal will then work when you swap out the variable for the column.

Please accept this as the solution once it works.

Re: Grand Total returning 0

xifeng_L — Fri, 17 May 2024 15:01:14 GMT

Hi jasemilly ,

The reason your previous measure was blank in the total row is because the SELECTEDVALUE function used by the _mins variable returned null on the total row. A blank value multiplied by any number always is blank, and therefore causes the final result to be blank.

I've improved the expression for the measure for you, you can try it.

Reimbursement = SUMX ( SUMMARIZE ( Journeys, Journeys[Key], Journeys[Bill], Journeys[Arrival Minutes Late] ), VAR _mins = Journeys[Arrival Minutes Late] VAR _perc = SWITCH ( TRUE (), _mins >= 21, 1, _mins >= 16, 0.8, _mins >= 11, 0.6, _mins >= 6, 0.4, _mins >= 1, 0.2 ) RETURN Journeys[Bill] * _perc )

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Grand Total returning 0

mark_endicott — Fri, 17 May 2024 16:06:40 GMT

xifeng_L - That's an excellent solution, and I'm now annoyed I didnt think of it! 👏