Cummulative Sum of % of grand total starting from the highest value (percentage) to lowest

[Mister_T]

Hi,

I am quite new to Power BI. I've read a few tutorials, browsed this forum, etc, but unfortunately I haven't found an answer to my question:

I would like to highlight the top rows of a descending clustered bar chart with in sum at least X % of the grand total.

When we are taking the example chart provided by Microsoft:

I do not want to highlight the top 3 stores but all the stores in descending order until the sum of their revenue is reaching X % of the grand total. All further stores should be grey.

What's an elegant way to do that?

Thanks a lot for your help!

PS: Based on the response of @amitchandak  (Thank you!) I have tried some DAX code I have found during my research (please see below), but unfortunately I am still not there.

[Mister_T]

I have now found another solution, which almost has brought me to my goal:

Total Amount % of total = SUM (Rev_Store[% of total])

Cumulative % descenting = 
VAR RevSum =
    ADDCOLUMNS(
        ALLSELECTED(Rev_Store[Store]),
        "amt", [Total Amount % of total]
    )
VAR CurrentRevAmount = [Total Amount % of total]
VAR RevLessThanCurren =
    Filter (
        RevSum,
        [amt] <= CurrentRevAmount
    )
RETURN
    SUMX (
        RevLessThanCurren,
        [amt]
    )

 
I am now almost there, but

1. it is starting cumulating the sum from the lowest value  --> how do I have to change the code to start from the highest value? (in my example the sum should start with 'B-Store', then add 'E-Store', ZD-Store, ...) 
2. the code I have found was based on absolute numbers. As I am counting shares, the "Total Amount % of total" will always be 1. How can I simplify the code based on me using percentages instead of absolute values?

Thanks a lot!

[amitchandak]

@Mister_T , They have used TOP N rank ,  and colored based on that.

You have create a percentile or cummlative % of total and color based on that

[Mister_T]

Thanks for your reply @amitchandak!

I have used a code for cumulating the percentage of each store's revenue I have found during my research:

Cumulative % = 
CALCULATE(SUM('Rev_Store'[% of total]),FILTER(
    ALLSELECTED('Rev_Store'[Store]),
    ISONORAFTER('Rev_Store'[Store],MAX('Rev_Store'[Store]),DESC)))

 
It gives me the cumulated sum, but based on alphabetical order and not starting from the highest share of the grand total to the lowest share as needed. The result looks as follows:

In my example the cumulative sum should start with B-Store, then E-Store, then ZD-Store ... and also highlight in this order.

How do I have to change the code in order to achieve this behavior?

Thank you very much!

[v-yiruan-msft · Accepted solution]

Hi @Mister_T ，

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column Rank base on the sort of %of total by descending

Rank = RANKX ( ALL ( 'Rev_Store' ), ( 'Rev_Store'[% of total] ),, DESC, DENSE )

2. Create another calculated column

Highlight = 
VAR _cumulative =
    CALCULATE (
        SUM ( 'Rev_Store'[% of total] ),
        FILTER (
            ALLSELECTED ( 'Rev_Store' ),
            'Rev_Store'[Rank] <= EARLIER ( 'Rev_Store'[Rank] )
        )
    )
VAR _sumoftotal =
    CALCULATE ( SUM ( 'Rev_Store'[% of total] ), ALL ( 'Rev_Store' ) ) * 0.8
RETURN
    IF ( _cumulative <= _sumoftotal, "Reach 80 % of the grand total", "Other" )

And you can select the field "%of total" and navigate to Column tools to set the format as "Percentage" as below screenshot:

Best Regards

[Mister_T]

@v-yiruan-msft 
Any idea how it comes that the red bars are slightly below the center of their row and the blue bars are slightly above, creating a smaller gap where they both meet than in the rest of the visualization?

 

[Mister_T]

Thank you very much @v-yiruan-msft! A little different than expected, but it worked 🙂

Thanks again! Very helpful!