Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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✌️!!
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 !!
Hi,
Thanks a lot. I just need to create RANK as a measure instead of having it as a column. Please assist.
Thanks!
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✌️ !!
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 !!
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✌️!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |