cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yekalo23
Helper II
Helper II

Percentage DESC count Until 80%

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%.  Percentage Count.PNG

2 ACCEPTED SOLUTIONS
parry2k
Super User III
Super User III

@Yekalo23 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.






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.





View solution in original post

parry2k
Super User III
Super User III

@Yekalo23 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] ) )






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.





View solution in original post

17 REPLIES 17
parry2k
Super User III
Super User III

@Yekalo23 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] ) )






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.





View solution in original post

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? 

parry2k
Super User III
Super User III

@Yekalo23 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.






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. 

TomMartens
Super User II
Super User II

Hey @Yekalo23 ,

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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?

parry2k
Super User III
Super User III

@Yekalo23 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.






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.error2.PNG

Ashish_Mathur
Super User III
Super User III

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User III
Super User III

@Yekalo23 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.






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.





View solution in original post

Hi @parry2k I am recieving the error below. Please advise. error1.png

littlemojopuppy
Super User I
Super User I

How are you ordering the percentages to get to 80%?  Biggest to smallest?  Reverse order of ID?

hi @littlemojopuppy 

I am doing biggest to smallest on the percentage column. 

Hi @Yekalo23...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.

Hi @littlemojopuppy 

Getting the error below when completeing the first meausre. 

error1.png

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors