Grand Total returning 0

[jasemilly]

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

[mark_endicott]

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

[xifeng_L · Accepted solution]

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~

[mark_endicott]

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