Cumulative totals

[frankhofmans]

hi all,

I want to create a cumulative table of the number of sickness cases and the duration of the cases. I have the following data table:

Employee ID	Absence in working days
101	2
519	3
147	9
231	12
445	3
362	4
9	15
169	7
377	5
291	5

I want to create the following outcome:

Days of absence	1	2	3	4	5	6	7	8	9	10	>10
# of cases	0	1	3	4	6	6	7	7	8	8	10
As % of total	0%	10%	30%	40%	60%	60%	70%	70%	80%	80%	100%

I tried to find the solution in previous posts, but i couldn't find the exact answer.

Thanks in advance,

Regards, Frank

[v-diye-msft · Accepted solution]

Hi @frankhofmans 

Plz let me know whether you'd like to get below one:

Measure = var a = IF(ISBLANK(COUNTROWS(FILTER('Table',[Absence in working days]<=1))),0,COUNTROWS(FILTER('Table',[Absence in working days]<=1)))
var b = COUNTROWS(FILTER('Table',[Absence in working days]<=2))
var c = COUNTROWS(FILTER('Table',[Absence in working days]<=3))
var d =  COUNTROWS(FILTER('Table',[Absence in working days]<=4))
var e = COUNTROWS(FILTER('Table',[Absence in working days]<=5))
var f = COUNTROWS(FILTER('Table',[Absence in working days]<=6))
var g = COUNTROWS(FILTER('Table',[Absence in working days]<=7))
var h = COUNTROWS(FILTER('Table',[Absence in working days]<=8))
var i = COUNTROWS(FILTER('Table',[Absence in working days]<=9))
var j = COUNTROWS(FILTER('Table',[Absence in working days]<=10))
var k = COUNTROWS(FILTER('Table',[Absence in working days]>10||[Absence in working days]<=10))
return SWITCH(TRUE(),MAX('Table2'[Days of absence])="1",a,MAX('Table2'[Days of absence])="2",b,MAX('Table2'[Days of absence])="3",c,MAX('Table2'[Days of absence])="4",d,MAX('Table2'[Days of absence])="5",e,MAX('Table2'[Days of absence])="6",f,MAX('Table2'[Days of absence])="7",g,MAX('Table2'[Days of absence])="8",h,MAX('Table2'[Days of absence])="9",i,MAX('Table2'[Days of absence])="10",j,k)

Measure 2 = DIVIDE([Measure],MAXX(ALL(Table2),[Measure]))

Pbix attached.

[amitchandak]

@frankhofmans , if [Absence in working days] is the measure. Then it is the case of dynamic segmentation. You need to create a bucket table for Days of absence, and the use measure to deal with it.

Please refer my video if that can help

https://youtu.be/CuczXPj0N-k

Or refer

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[frankhofmans]

hi,

I can't replicate the correct dax formula from your Youtube video. Could you help me further with the needed formula(s)?

Thanks in advance,

Regards, Frank

[amitchandak]

@frankhofmans , Try like

Brand Bucket = divide(Countx(filter(VALUES('Table'[Employee ID]),[Absence in working days] >=Min('Days Bucket'[Start Limit]) && [Margin %] <max('Days Bucket'[End Limit])),'Table'[Employee ID]),calculate('Table'[Employee ID],allselected(Table)))

Brand Bucket = divide(Countx(filter(VALUES('Table'[Employee ID]),[Absence in working days] >=Min('Days Bucket'[Start Limit]) && [Margin %] <max('Days Bucket'[End Limit])),'Table'[Employee ID]),calculate('Table'[Employee ID]))

Bucket will 1-1, 1-2 till 10 and 10 -10000 <Big Number>

[frankhofmans]

hi,

thanks, i'm getting closer. But my absence in working days isn't a measure, but a calculated column in the data table. Do you have suggestion for that?

thanks!