Forum Discussion

brandonzlotnik's avatar
brandonzlotnik
Regular Visitor
1 year ago
Solved

Cumulative Count by Category, Sorted by Frequency

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!

category
CUMULATIVE COUNT
frequency
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

2 Replies

  • Gabry's avatar
    Gabry
    Icon for Super User rankSuper User
    1 year ago

    Hello,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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