80-20 distribution of data

[NILU090]

Hi Team,

I have following data. 

I have created chart countrywise & city wise. there are couple of other filters too.

I want to show data in 80-20 ration.

1. state wise total vs 80% amount

2. statewise total vs 80% countrows.

please help me on this

[v-xingshen-msft · Accepted solution]

Hi @NILU090 ,
In your mind, you're trying to use the 80-20 rule to figure out that 80 percent of sales are made in the top 20 percent of states, right?
In the example data you gave, we used CITY as a demo to help you with your question due to the small variety of STATES. We first use the co-ordering of amounts and cities to ensure that we are adding up from the largest amount, then we add up the values for each different city until we get to 80 percent, and finally find out which cities make up 80 percent of the total.

City rank = RANKX(ALL('80_20_data'),CALCULATE(MAX('80_20_data'[Amount]),ALLEXCEPT('80_20_data','80_20_data'[City])),,DESC) 

Cumulative Amount = 
DIVIDE(
    CALCULATE(SUM('80_20_data'[Amount]),FILTER(ALL('80_20_data'),'80_20_data'[City rank]<=MAX('80_20_data'[City rank]))),CALCULATE(SUM('80_20_data'[Amount]),ALL('80_20_data')),0)

I hope my answer can solve your questions, if you still have more recent questions, you can feel free to contact me me, I will be the first time to reply you after receiving your message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[grazitti_sapna]

Hi @NILU090 ,
To achieve an 80-20 split, you need to calculate the total amount or countrows for each state and then identify the threshold where 80% of the total is reached.

• Here are the DAX measures to calculate this:
  
  1. StatewiseTotalAmount = CALCULATE(SUM(Table[Amount]), GROUPBY(Table, Table[State]))
  2. Statewise80Amount = CALCULATE(SUM(Table[Amount]) * 0.8, GROUPBY(Table, Table[State]))
  3. StatewiseTotalCountrows = CALCULATE(COUNTROWS(Table), GROUPBY(Table, Table[State]))
  4. Statewise80Countrows = CALCULATE(COUNTROWS(Table) * 0.8, GROUPBY(Table, Table[State]))
• Create a table or chart:
  • Add State to the rows.
  • Add StatewiseTotalAmount and Statewise80Amount as columns for the first requirement.
  • Add StatewiseTotalCountrows and Statewise80Countrows as columns for the second requirement.
    
    

If I have resolved your question, please consider marking my post as a solution🎉. Thank you!