TOP N Slicer based on cumulative contribution

[Omega]

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!

[VahidDM · Accepted solution]

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:

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✌️!!

[Omega]

Hi,

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

Thanks!

[VahidDM]

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

[VahidDM · Accepted solution]

@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 · Accepted solution]

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:

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✌️!!