Cumulative Total over categorical variables

[Anonymous]

Hello there,

I have a table of transactional data of many columns. The two I want to focus on are Customer Category and Sales.

I would like to create a chart which shows the distribution of cumulative sales by customer category so I can make a statment like "50% of sales come from 2 customer categories"

The way I envisage visualising this is a line chart with Customer Category on the x axis and Cumulative Sales on the y axis. 

How would I create a measure to calculate cumulative sales over this categoric variable?

Thank you for your help in advance

[Anonymous]

If you're going to be showing percentages, I would recommend using a bar chart instead of a line chart (Line charts are great for time series, not great for percentages).

There are potentially 2 ways you can get what you're looking for. 
1. Write a simple SUM formula to get the sum of sales, then use the category column in the legend of the bar chart. You can then switch the label to show % of total instead of the actual value. 

2. Write a formula like this:

Measure = 

DIVIDE (
 SUM( Table[Sales]),
 CALCULATE ( 
   SUM( Table[Sales]),
   ALL( Table[Category])
)
)

[Anonymous]

Thank you so much for your reply. 

Just one question, how would I show this as a cumulative total? I am interested to know what proportion of sales my top 1 / top 2 / top 3 etc customer groups make up.

Is there a way of doing it so I wouldn't have to sum them manually?

[Anonymous]

Hi @Anonymous ,

You can use TopN function on top of measure @Anonymous suggested

More on the topic:

https://www.youtube.com/watch?v=ljAINOUt-X8