Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jasemilly
Helper II
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
xifeng_L
Solution Sage
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~

 

 

View solution in original post

3 REPLIES 3
mark_endicott
Solution Specialist
Solution Specialist

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

xifeng_L
Solution Sage
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~

 

 

mark_endicott
Solution Specialist
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.  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors