Forum Discussion

67nmark's avatar
67nmark
Icon for Helper I rankHelper I
2 years ago

Make running total conditional on a slicer selection

Hello,

I'm struggling to make a running total measure that works with a slicer selection and am hoping someone can put me on the right track. The problem is I need a single measure that works when I either select a value in a slicer or have no value selected. At the moment, I have created 2 measures, measure 1 works when a xpm_invoices[Invoiced Amount] slicer value is selected (but not when no value is selected). Measure 2 works when no xpm_invoices[Invoiced Amount] slicer value is selected (but not when a value is selected). I'd be very grateful for some assistance with this if anyone can help.

 

Cheers,
Mark

 

TotalAmountByClientGroup = SUM(xpm_invoices[Invoiced Amount])

 

Measure 1 =

VAR tot_amount = [TotalAmountByClientGroup]
VAR RunningTotal =
    CALCULATE (
        [TotalAmountByClientGroup],
        FILTER (
            ALLSELECTED(xpm_invoices[[Client]] Group(s)]),  -- Remove filters only from Group(s) while respecting slicer context for Account Manager
            [TotalAmountByClientGroup] >= tot_amount
        )
    )
RETURN
RunningTotal
 
Measure 2 =
VAR tot_amount = [TotalAmountByClientGroup]
RETURN
CALCULATE (
[TotalAmountByClientGroup],
FILTER (
ALL ( xpm_invoices[[Client]] Group(s)],xpm_invoices[[Client]] Account Manager] ),
[TotalAmountByClientGroup] >= tot_amount
"DAX "

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 67nmark ,

    Can you provide a .pbix file without sensitive data, or provide simple data and show the expected results in pictures? If possible, I would also like to know which slicers are used and which fields are used in the visual.

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

     

    Since I don't have enough information, I can only think of one possible solution.

    Use the following DAX expression to create a measure

    Measure 3 = 
var _A = CALCULATE(COUNTROWS(VALUES('xpm_invoices'[Invoiced Amount])),ALL('xpm_invoices'))
VAR _B = CALCULATE(COUNTROWS(VALUES('xpm_invoices'[Invoiced Amount])),ALLSELECTED('xpm_invoices'))
RETURN IF(_B <> _A,[Measure 1],[Measure 2])

     

    Best Regards,
    Wenbin Zhou