Forum Discussion

denxx34's avatar
denxx34
Frequent Visitor
3 years ago
Solved

Calculate Running AVG Cumulative over groups

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_idSvc_name
SV0014407A
SV0018844B
SV0020838C
SV0024112sdf
SV0028077gds
SV0000572degf
SV0000762hwsa
SV0000741edgfr
SV0001435hds
SV0001870adsdf
SV0002316hbdfg
SV0002692dfrghh
SV0003552asdggha
SV0003827ghaadgf
SV0003852gdaag
SV070004171 ghaadfgf

 

FactSvcCall

Call_id Obj_idSvc_idDate_call     DaysBetweenFailure i_index
1SO1002438 SV001440716.12.20198401
2SO1002438SV001884427.07.20202242
3SO1002438SV002083823.11.20201193
4SO1002438SV002411211.05.20211694
5SO1002438SV002807724.11.20211975
6SO2076722SV000057213.02.20213451
7SO2076722SV000076222.03.202163
8SO2076722SV000074116.03.2021312
9SO2076722SV000143505.07.20211054
10SO2076722SV000187022.09.2021795
11SO2076722SV000231606.12.2021756
12SO2076722SV000269209.02.2022657
13SO2076722SV000355208.06.20221198
14SO2076722SV000382715.07.2022379
15SO2076722SV000385222.07.2022710
16SO2076722SV070004171  05.09.2022 4511

 

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?

 

Need Help
Show and Tell
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    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