Calculate Running AVG Cumulative over groups

[denxx34]

Hello everyone, I hope you can help me... thanks in advance!!

The following scenario: We have objects. When these objects break, a service call is recorded. It's about calculating the average downtime.

There are the master data tables: service call & object.
A fact table FactSvcCall that provides the number of down days and the order.
And there is calendar table.

Sample Data:

Object

Obj_id	Obj_name
SO1002438	Abc Def
SO2076722	Ghi Jk

Service call

Svc_id	Svc_name
SV0014407	A
SV0018844	B
SV0020838	C
SV0024112	sdf
SV0028077	gds
SV0000572	degf
SV0000762	hwsa
SV0000741	edgfr
SV0001435	hds
SV0001870	adsdf
SV0002316	hbdfg
SV0002692	dfrghh
SV0003552	asdggha
SV0003827	ghaadgf
SV0003852	gdaag
SV070004171	ghaadfgf

FactSvcCall

Call_id	Obj_id	Svc_id	Date_call	DaysBetweenFailure	i_index
1	SO1002438	SV0014407	16.12.2019	840	1
2	SO1002438	SV0018844	27.07.2020	224	2
3	SO1002438	SV0020838	23.11.2020	119	3
4	SO1002438	SV0024112	11.05.2021	169	4
5	SO1002438	SV0028077	24.11.2021	197	5
6	SO2076722	SV0000572	13.02.2021	345	1
7	SO2076722	SV0000762	22.03.2021	6	3
8	SO2076722	SV0000741	16.03.2021	31	2
9	SO2076722	SV0001435	05.07.2021	105	4
10	SO2076722	SV0001870	22.09.2021	79	5
11	SO2076722	SV0002316	06.12.2021	75	6
12	SO2076722	SV0002692	09.02.2022	65	7
13	SO2076722	SV0003552	08.06.2022	119	8
14	SO2076722	SV0003827	15.07.2022	37	9
15	SO2076722	SV0003852	22.07.2022	7	10
16	SO2076722	SV070004171	05.09.2022	45	11

In addition to the average, I also need the MIN and MAX over the course of time... also cumulatively.

The aim is to achieve the following overview:

The group is Svc_id and always calculate the sum of DaysBetweenFailure divided by the number starting from 1 date.

The data model:

Is there a way to calculate measures (Avg, Min, Max) so that you can then also flexibly filter based on the data model, i.e. get values from dimension tables in the normal way?

[v-easonf-msft · Accepted solution]

Hi, @denxx34 

Please try formula like below:

cmulatively count =
CALCULATE (
    DISTINCTCOUNT ( FactSvcCall[i_index] ),
    FILTER (
        ALLSELECTED ( FactSvcCall ),
        FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
            && FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
    )
)

cumulatively sum =
CALCULATE (
    SUM ( FactSvcCall[DaysBetweenFailure] ),
    FILTER (
        ALLSELECTED ( FactSvcCall ),
        FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
            && FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
    )
)

Avg = [cumulatively sum]/[cmulatively count] 

Max =
CALCULATE (
    MAX ( FactSvcCall[DaysBetweenFailure] ),
    FILTER (
        ALLSELECTED ( FactSvcCall ),
        FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
            && FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
    )
)

Min =
CALCULATE (
    MIN ( FactSvcCall[DaysBetweenFailure] ),
    FILTER (
        ALLSELECTED ( FactSvcCall ),
        FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
            && FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
    )
)

Best Regards,
Community Support Team _ Eason