Running total of measure

[kjohn10]

I have monthly sales data that I would like to dynamically calculate a running total of % sales of each products overall total % sales over all products in a filterable period (combine year-month into a 'period' column). Simplified data set would be as follows:

I start with a measure to get the sum of sales for a product:

SumSales = SUM( 'SalesTable'[Sales] )

Then a measure for the total sales selected (since the data can be filtered on date range, e.g. YYYY-MM):

TotalSales = CALCULATE( SUM( 'SalesTable'[Sales] ), ALLSELECTED( 'SalesTable' ) )

Now the final measure to calculate the % of a products sales over the total:

% Sales = CALCULATE( DIVIDE( [SumSales], [TotalSales] ) ) * 100

This works just fine and I can sort the resulting table in PowerBI by % Sales from high to low.

 

What I want now is a running total of that % Sales measure. I need this so I can then set a measure that categorizes products that make up the top 80% sales. The example data here is a bit short to get good results, but I cannot get a running total to work. I also setup a 'DateKey' Table (Calendar) to use, but havent linked it to the sales table yet...I'll likely need that in the future. If I can use just the Sales[Period] for FILTER parameters, that is fine. Although that might not be the right filter parameter to use...?

I've tried various methods / suggestions from other posts here, but none work. For example, tried:

RunningTotal = SUMX( FILTER( ALLSELECTED( 'SalesTable' ), 'SalesTable'[Period] <= MAX( 'SalesTable'[Period] ) ), [% Sales]

Adding that to the table results in 100% for every row, not a running sum. I tried replacing the [Period] with [Sales] column, and still no good.

Then tried to first setup a 'cummulative' measure:

SalesCumm = CALCULATE( [% Sales], FILTER( ALLSELECTED( 'SalesTable' ), 'SalesTable'[Sales] <= MAX( 'SalesTable'[Sales] ) ) )

Then the running measure as:

RunningSales = DIVIDE( [SalesCumm], CALCULATE( [% Sales], ALLSELECTED( 'SalesTable' ) ), BLANK() )

And that also results in 100% for every row, not a running sum.

What I am missing here? from other posts it seems easy enough but I cannot get it right. Once I have this running sum measure, I'll define a categorization measure like: Category = IF('SalesTable'[RunningSales] > 80, "A", "B") which is the end-state of this report. Just need to get this running total working...

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[kjohn10]

Try this pbix file; I've added a couple more 'products' to give it more results.

[Ashish_Mathur]

Hi @kjohn10,

You may download my solution workbook from here.  You will be able to slice this data by any field - i have also created a calendar Table there.  You may download my solution workbook from here.

Hope this helps.

[Ashish_Mathur]

Hi @kjohn10,

You may download my solution workbook from here.  You will be able to slice this data by any field - i have also created a calendar Table there.  You may download my solution workbook from here.

Hope this helps.

[Zubair_Muhammad]

@kjohn10

Try This MEASURE

RunningTotal =
VAR CurrentPercent = [% Sales]
RETURN
    SUMX (
        FILTER ( ALL ( Sheet1[Product] ), [% Sales] >= CurrentPercent ),
        [SumSales] / CALCULATE ( SUM ( Sheet1[Sales] ), ALL ( Sheet1 ) )
    )

[Zubair_Muhammad]

@kjohn10

 

[gvg]

@Zubair_Muhammad

What is [SumSales] here?

[kjohn10]

@Zubair_Muhammad Thanks, works without filters; I've managed to update the measure so that I can get it working with a Date filter: Made it 'ALLSELECTED' at start of filter, and ALL(Sheet1[Product]) at the end. Now I can filter on a date range and the running total will adjust correctly.

RunningTotal = 
VAR CurrentPercent = [% Sales]
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( Sheet1[Product] ), [% Sales] >= CurrentPercent ),
        [SumSales] / CALCULATE ( SUM ( Sheet1[Sales] ), ALL ( Sheet1[Product] ) )
    )

Thanks for getting me 95% there!

[kjohn10]

Unfortunately these measure don't work when adding other attributes of the [Product] to the table:

The FILTER params need adjustment, but I cannot get anything to work when any other column (attribute) is added to the table. Updated pbix here for example. I know I already marked this with a correct answer, which did solve the initial problem. I'd hate to post another question...hopefully taking a look at the example pbix wont take too much time to nail down what needs to change to the measure.

[Zubair_Muhammad · Accepted solution]

@kjohn10

Try this revision

RunningTotal =
VAR CurrentPercent = [% Sales]
RETURN
    SUMX (
        FILTER (
            ALL ( Sheet1[Class], Sheet1[Product], Sheet1[Identifier] ),
            [% Sales] >= CurrentPercent
        ),
        [SumSales]
            / CALCULATE (
                SUM ( Sheet1[Sales] ),
                ALL ( Sheet1[Class], Sheet1[Product], Sheet1[Identifier] )
            )
    )

[kjohn10]

Yes, that's it; makes sense now that I've read through some more of the FILTER functionality.

Thanks again for helping!