Forum Discussion
Simple Cumulative Count/Percentage Matrix
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
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.
Regards,
Daniel He
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
2 Replies
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
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.
Regards,
Daniel He