cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Omega
Impactful Individual
Impactful Individual

TOP N Slicer based on cumulative contribution

Hi, 

 

I have the below sales per item and the contribution of each based on total sales. I want to create a slicer that calculates the top cumulative contribution %. For example, when the user selects 50%, the table will show items: C G K D because there total is around 43% which is closest to 50%. 

 

Please advise on how to create such column. 

 

Thanks!

 

Capture.PNG

2 ACCEPTED SOLUTIONS
VahidDM
Super User
Super User

Hi @Omega 

 

First, add a new parameter as a percentage or create a new table with "Percentage = GENERATESERIES(0, 1, 0.01)"., then try this measure:

 

TOP N % =
VAR _Perc =
    SELECTEDVALUE ( Percentage[Percentage], "100" )
VAR _Cum =
    CALCULATE (
        SUM ( 'Table'[Contribution %] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Rank] <= MAX ( 'Table'[Rank] ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Contribution %] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Item #] ), _Cum <= _Perc )
    )

 

Output:

VahidDM_0-1631172067701.png

 

Link to download the sample file: https://gofile.io/d/BTUjjH

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

View solution in original post

@Omega 

You can add a column with this code:

Rank Column =
RANKX ( ALL ( 'Table' ), 'Table'[Contribution %],, DESC, DENSE )

Or as a measure with this code:

Rank Measure =
RANKX (
    ALL ( 'Table' ),
    CALCULATE ( MAX ( 'Table'[Contribution %] ) ),
    ,
    DESC,
    DENSE
)

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos !!

 

View solution in original post

4 REPLIES 4
Omega
Impactful Individual
Impactful Individual

Hi,

 

Thanks a lot. I just need to create RANK as a measure instead of having it as a column. Please assist. 

 

Thanks!

@Omega 

 

If you want to add a Contribution column with DAX, use this code to add a new column:

Contribution Column =
VAR _TotalAmount =
    CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) )
RETURN
    FORMAT ( 'Table'[Value] / _TotalAmount, "Percent" )

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️ !!

@Omega 

You can add a column with this code:

Rank Column =
RANKX ( ALL ( 'Table' ), 'Table'[Contribution %],, DESC, DENSE )

Or as a measure with this code:

Rank Measure =
RANKX (
    ALL ( 'Table' ),
    CALCULATE ( MAX ( 'Table'[Contribution %] ) ),
    ,
    DESC,
    DENSE
)

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos !!

 

VahidDM
Super User
Super User

Hi @Omega 

 

First, add a new parameter as a percentage or create a new table with "Percentage = GENERATESERIES(0, 1, 0.01)"., then try this measure:

 

TOP N % =
VAR _Perc =
    SELECTEDVALUE ( Percentage[Percentage], "100" )
VAR _Cum =
    CALCULATE (
        SUM ( 'Table'[Contribution %] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Rank] <= MAX ( 'Table'[Rank] ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Contribution %] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Item #] ), _Cum <= _Perc )
    )

 

Output:

VahidDM_0-1631172067701.png

 

Link to download the sample file: https://gofile.io/d/BTUjjH

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.