Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
kjohn10
Helper I
Helper I

Running total of measure

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:

 

NetSales.png

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.

 PercentSales.png

 

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

1 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] )
            )
    )

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Untitled.png

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

What is [SumSales] here?

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

 

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

PercentSales2.png

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.

@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] )
            )
    )

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

Thanks again for helping!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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