Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have been trying to solve this problem and I cannot get to the root of it. As you can see on the excel example, I have a table that contains ID, Cost, and Percentage columns. I want to be able to create a measure where I am able to change the percentage column to DESC and where I am able to find the count of the rows that make up DESC Percentage =<80%.
Solved! Go to Solution.
@Anonymous you can achieve by using RANK measure and running total , here are measures:
//let rank the percent measure
Percent Rank = RANKX ( ALL ( 'Table' ), [Percent], , DESC )
//get the running total of percent based on rank, highest to lowest
Percent Running Total =
VAR __rank = [Percent Rank]
RETURN
SUMX ( FILTER ( ALL ( 'Table' ), [Percent Rank] <= __rank ), [Percent] )
//final measure to count rows that are below threshold in this case 0.8 or 80%
Row Count Below Threshold =
VAR __countBelowPercent = 0.8 --store threshold percentabge
RETURN
COUNTX (
'Table',
IF ( [Percent Running Total] <= __countBelowPercent, 1 )
)
Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous don't paste images but rather paste the code, it is easy to work with. Follow some basic rules so that it easy for everyone.
In Rank measure, the column you are using for a percent is a column or a measure, I assumed it is a measure, if it is not then change it to something like CALCULATE ( MAX ( YourTable[YourPercent] ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous don't paste images but rather paste the code, it is easy to work with. Follow some basic rules so that it easy for everyone.
In Rank measure, the column you are using for a percent is a column or a measure, I assumed it is a measure, if it is not then change it to something like CALCULATE ( MAX ( YourTable[YourPercent] ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k,
The count was able to work, thank you. I had a follow up question. When introducing filters, I see that is is not able to auto calculate the new count based on lets say a selected country. Is this something that is possible?
@Anonymous I'm still not sure why the measure I gave you doesn't work, You never provided any details when I asked. How you expect someone to help you if you cannot answer on the existing solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
HI @parry2k Sorry I did not see your response until you tagged my name. I have responded and thank you so much for your help. This has been a frustrating task.
Hey @Anonymous ,
here is another approach that does not use the RANKX function:
Measure =
var _p = CONCATENATEX( SELECTCOLUMNS( 'Table' , "p" , [percentage] ) , [p] , "|" , [p] , DESC)
var _p_Length = PATHLENGTH( _p )
var t0 =
ADDCOLUMNS(
GENERATESERIES( 1 , _p_Length )
, "percentagevalue" , VALUE( PATHITEM( _p , [Value] , TEXT ) )
)
var t1 =
ADDCOLUMNS(
t0
, "percentagevalueCumulated" ,
var index = [Value]
return
SUMX( FILTER( t0, [Value] <= index) , [percentagevalue] )
)
return
COUNTROWS(
FILTER( t1, [percentagevalueCumulated] <= 0.8 )
)
This can have an impact on performance depending on the size of your data model.
In regards to provide input on behalf of your error message without a PBIX that contains sample data but still reflect your data model providing help is almost impossible (at least not for me).
Regards,
Tom
Hi @TomMartens ,
I have 561,000 rows. The performance will be very important because I will be using filters. When running the measure you gave, it has been stuck in loading. Any advice?
@Anonymous share the full dax expression and the data model, it is not easy to debug from an image of an error. Not sure what I'm looking at.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k , I have attached the full dax expression below I have replaced the 'table' by CCT_LIMITS_IN_USD+PERCENT_BUCKETS and the '[percentage]' by my percentage column.
Hi,
Share the link from where i can download your PBI file.
@Anonymous you can achieve by using RANK measure and running total , here are measures:
//let rank the percent measure
Percent Rank = RANKX ( ALL ( 'Table' ), [Percent], , DESC )
//get the running total of percent based on rank, highest to lowest
Percent Running Total =
VAR __rank = [Percent Rank]
RETURN
SUMX ( FILTER ( ALL ( 'Table' ), [Percent Rank] <= __rank ), [Percent] )
//final measure to count rows that are below threshold in this case 0.8 or 80%
Row Count Below Threshold =
VAR __countBelowPercent = 0.8 --store threshold percentabge
RETURN
COUNTX (
'Table',
IF ( [Percent Running Total] <= __countBelowPercent, 1 )
)
Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
How are you ordering the percentages to get to 80%? Biggest to smallest? Reverse order of ID?
Hi @Anonymous...was working on this and then saw that @parry2k already posted a solution that's correct. But here's what I had come up with...
Cumulative Percentage =
VAR CostRank =
RANKX(
ALLSELECTED(Table1),
[Total Cost],
[Total Cost],
DESC,
Skip
)
VAR ParetoSummary =
SUMMARIZE(
ALLSELECTED(Table1),
Table1[ID],
"CostRanking",
RANKX(
ALLSELECTED(Table1),
[Total Cost],
[Total Cost],
DESC,
Skip
),
"PercentTotalCost",
[Cost % Grand Total]
)
RETURN
SUMX(
FILTER(
ParetoSummary,
[CostRanking] <= CostRank
),
[PercentTotalCost]
)
This doesn't give you the "how many records until I cross 80%" part though 😐 No point in continuing since the other solution is correct.
DAX for the measure you're trying? @parry2k and I offered two very different approaches
I was trying @parry2k measure and received the error. Are you getting the same error from your end?
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |