topic Re: Cumulative Total over categorical variables in DAX Commands and Tips

Cumulative Total over categorical variables

Anonymous — Mon, 03 Feb 2020 18:59:38 GMT

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

Re: Cumulative Total over categorical variables

Anonymous — Tue, 04 Feb 2020 05:19:14 GMT

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]) ) )

Re: Cumulative Total over categorical variables

Anonymous — Tue, 04 Feb 2020 08:29:42 GMT

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?

Re: Cumulative Total over categorical variables

Anonymous — Tue, 04 Feb 2020 11:26:28 GMT

Hi Anonymous ,

You can use TopN function on top of measure Anonymous suggested