Cumulative Count by Category, Sorted by Frequency

[brandonzlotnik]

Hello. I have data that needs to be arranged by frequency (how many claims have occured) per location. The result is a bar graph with the locations with the most claims appearing first:

Nice! First part is done.

I then need to create a line that cumulatively sums up the total number of claims for each location, then displays it as a percentage of the total number of claims (for the filtered timeframe). Hypothetically it would look something like thisq (this screenshot comes from Excel):

However I cannot seem to get the line to cooperate. My last attempt ended up looking like this:

Any feedback on how to make this line function like the one above would be much appreciated.

Thank you!

[Anonymous · Accepted solution]

Hi @brandonzlotnik ,

The point is that we need to calculate the sum of the previous bars based on the current bar. However, your current X-axis is out of order. Is there a column in your data that can be used to determine the order of the X-axis?

If your table does not have an index column for the X axis, you can refer to my test.

1. Create calculated columns.

sum = CALCULATE(SUM('Table'[random]),ALL('Table'),'Table'[Loc]=EARLIER('Table'[Loc]))//calculate sum of each loc

rank = RANKX(ALL('Table'),'Table'[sum])//rank loc according to sum of each loc

2. Create measure to calculate ratio.

Measure = VAR _sum = CALCULATE(SUM('Table'[sum]),ALL('Table'))
VAR _previous = CALCULATE(SUM('Table'[sum]),ALL('Table'),'Table'[rank]<=MAX('Table'[rank]))
RETURN _previous/_sum

3. Output.

Best regards,

Mengmeng Li

[Gabry]

Hello,

you should provide your dax formulas and also if possible some sample data