Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

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

View solution in original post

parry2k
Super User
Super User

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

View solution in original post

17 REPLIES 17
parry2k
Super User
Super User

@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
Not applicable

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
Super User

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

Anonymous
Not applicable

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
Super User

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



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
Anonymous
Not applicable

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
Super User

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

Anonymous
Not applicable

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
Super User

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
Super User

@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
Not applicable

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

littlemojopuppy
Community Champion
Community Champion

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

Anonymous
Not applicable

hi @littlemojopuppy 

I am doing biggest to smallest on the percentage column. 

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

I was trying @parry2k measure and received the error. Are you getting the same error from your end?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.