Simple Cumulative Count/Percentage Matrix

[sotoc]

I'd like to learn the best way to create a 4 column matrix with counts of a field, % of the count, cumulative count and cumulative %.

Here's what it looks like in Excel:

Table data is in Dropbox:  https://www.dropbox.com/s/v1qbun4v8g78wh5/SampleData_CumulativeMatrix.csv?dl=0

My current DAX Measure for Count of Code = COUNT('Table'[Code])
For % I am using %GT Count of Code in Values, if a measure works better please advise

My cumulative count and cumulative measures are a mess because I need to sort by code desc.  I created a sorting table to order by desc, sorting Code by Index:

T

able data is in attached Dropbox link. Thank you!
Carly

[v-danhe-msft · Accepted solution]

Hi @sotoc,

Based on my test, you could refer to below steps:

Format the [Code] column from text to whole number.

Create two measures:

cumulative count = CALCULATE(SUM(SampleData_CumulativeMatrix[Count of Code]),FILTER(ALL('SampleData_CumulativeMatrix'),'SampleData_CumulativeMatrix'[Code]>=MAX('SampleData_CumulativeMatrix'[Code])))

cumulative% = [cumulative count]/CALCULATE(SUM(SampleData_CumulativeMatrix[Count of Code]),ALL(SampleData_CumulativeMatrix))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/m0xv4vji1nuli6g/Simple%20Cumulative%20Count%20Percentage%20Matrix.pbix?dl=...

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @sotoc,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @sotoc,

Based on my test, you could refer to below steps:

Format the [Code] column from text to whole number.

Create two measures:

cumulative count = CALCULATE(SUM(SampleData_CumulativeMatrix[Count of Code]),FILTER(ALL('SampleData_CumulativeMatrix'),'SampleData_CumulativeMatrix'[Code]>=MAX('SampleData_CumulativeMatrix'[Code])))

cumulative% = [cumulative count]/CALCULATE(SUM(SampleData_CumulativeMatrix[Count of Code]),ALL(SampleData_CumulativeMatrix))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/m0xv4vji1nuli6g/Simple%20Cumulative%20Count%20Percentage%20Matrix.pbix?dl=...

Regards,

Daniel He