cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Grand Total returning 0

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

1 ACCEPTED SOLUTION
Solution Sage

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~

3 REPLIES 3
Solution Specialist

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

Solution Sage

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~

Solution Specialist

@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.