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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Regards
Zubair

Please try my custom visuals

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

Regards
Zubair

Please try my custom visuals

@kjohn10

 

 5231.png


Regards
Zubair

Please try my custom visuals

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

Regards
Zubair

Please try my custom visuals

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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