cancel
Showing results for
Did you mean:
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%.

Thanks!

2 ACCEPTED SOLUTIONS
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:

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

Super User

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.

4 REPLIES 4
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!

Super User

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.

Super User

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.

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:

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

Announcements

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

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

#### 2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors