Power BI - Running Totals with Filters and Slicers

2
votes

I want to create running totals (using a measure). However, if I apply slicers I want the values to adjust according to this selection.

For example I have a table with my clothes purchases for 2017-2018 (date of purchase;brand of product; product type; cost amount).

If I slice for Hugo Boss brand; year 2018 I want my March value to retrieve the sum of costs of all Hugo Boss products between first day of the raw table (in this case 1st of January 2017) and the last day of the month in my selection (in this case: 31st of March 2018).

At the moment my function is as shown below but does not work.

Cumulative =
CALCULATE (
    SUM ( 'Clothes Purchases'[Cost] ),
    FILTER (
        ALL ( 'Clothes Purchases' ),
        'Clothes Purchases'[Acctg Date] <= MAX ( 'Clothes Purchases'[Acctg Date] )
    )
)

Can someone please help?

Thanks, Domingos

3
powerbidaxcumulative-sum

3 Answers

1
votes

I created a simple table according to the values you require:

Date of Purchase    Brand of Product    Product Type    Cost Amount
03/03/2018          Hugo Boss           Watch           $300
01/05/2017          Kate Spade          Jeans           $130
28/07/2017          Givenchy            Perfume         $212
19/10/2017          Adidas              Activewear      $179

And then created a measure on the same table in Report mode, using:

Running Total = CALCULATE(SUM('Clothes Purchases'[Cost Amount]),
                          FILTER(ALLSELECTED('Clothes Purchases'[Brand of Product]),
                          ISONORAFTER('Clothes Purchases'[Brand of Product],
                          MAX('Clothes Purchases'[Brand of Product]), DESC)))

Then using a slicer on Brand of Product and Date of Purchase, here is the running total without any values chosen in the slicers (test by adding all of the costs together): Running Total without Slicers

And here is the running total with just "Hugo Boss" chosen on the slicer according to the dates you require: Running Total with Slicers

I hope this helps! :)

1
votes

First, create a calculated column in the table for the Year-to-Date total, you will reference this later in your measure:

Cumulative Cost = TOTALYTD(SUM('Clothes Purchases'[Cost Amount],'Clothes Purchases'[Date])

To filter down use the ALLEXCEPT clause in your measure and specify the filter columns:

RunningTotalMeasure = CALCULATE( SUM( Clothes Purchases'[Cumulative Cost] ),
    FILTER( ALLEXCEPT( 'Clothes Purchases'[Brand of Product], 'Clothes Purchases'[Product Type]), 
    'Clothes Purchases'[Date] <= MAX( 'Clothes Purchases'[Date] ) ) )

I recently experienced this issue as well and have had sleepless nights over it (props to the gems scattered here and there). I hope it is of help.

0
votes

You can simply change your ALL to ALLSELECTED