Hi Everyone
I have a Powerpivot chart and table in an Excel sheet. The chart/table show cumulative percentage across a range of months on the x-axis. On the x axis, the range of months is: 0,1,2,3,4,5,6,12,18,24,30,36...... I would like to have the rows and data for the 1,2,3,4,5 rows available, but I don't typically display them, only the 6 month intervals.
I started using a DAX forumula below which cumulatives the numbers into a percent, but the problem is that I have to show all the months on the X-axis (including 1-5 values) for it to cumulate the numbers into percents correctly. If I hide the 1-5, it does not add the values for these into the overall cumulative percent.
Has anyone come across this before or any ideas to modify the below formula to accommodate this?
Paul
=(CALCULATE(count(s1Counties[entity_id]),FILTER(ALLSELECTED(s1Counties),s1Counties[exitMonthCategory] <=MAX(s1Counties[exitMonthCategory]))))/
(CALCULATE(COUNTROWS(s1Counties),ALL(s1Counties[exit],s1Counties[exitMonthCategory])))
I have a Powerpivot chart and table in an Excel sheet. The chart/table show cumulative percentage across a range of months on the x-axis. On the x axis, the range of months is: 0,1,2,3,4,5,6,12,18,24,30,36...... I would like to have the rows and data for the 1,2,3,4,5 rows available, but I don't typically display them, only the 6 month intervals.
I started using a DAX forumula below which cumulatives the numbers into a percent, but the problem is that I have to show all the months on the X-axis (including 1-5 values) for it to cumulate the numbers into percents correctly. If I hide the 1-5, it does not add the values for these into the overall cumulative percent.
Has anyone come across this before or any ideas to modify the below formula to accommodate this?
Paul
=(CALCULATE(count(s1Counties[entity_id]),FILTER(ALLSELECTED(s1Counties),s1Counties[exitMonthCategory] <=MAX(s1Counties[exitMonthCategory]))))/
(CALCULATE(COUNTROWS(s1Counties),ALL(s1Counties[exit],s1Counties[exitMonthCategory])))
