Measure calculation of cumulative progress %

[jeongkim]

Hi,

How can I create measures to calculate cumulative % results?

e.g.

M1 calculate stage B/C progress with value with M1.

M2 calculate stage B/C progress with value with M1 and M2 total.

M3 calculate stage B/C progress with value with M1 and M2 and M3 total.

So would like to get each month's status of how many counts of default valuues compared to how much progressed % of stage B/C.

I have date table sepearately as 'Date'[Date].

[SundarRaj]

Hi @jeongkim ,
I don't know how your data model looks like but this is a high level picture of the view that you desire. We can automate it further as well. Do have a look at it and let me know in case of any issues. Thanks

I'll attach the file for reference.

Regards,

[Praful_Potphode]

Hi @jeongkim 

Can you provide more information(input,calculation formula in excel,output) here?
What is the input data in PBI vs Expected Output?

[Jihwan_Kim · Accepted solution]

Hi, I am not sure how your semantic model looks like, but one of ways is to use WINDOW DAX function in the measure.

WINDOW function (DAX) - DAX | Microsoft Learn

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

Value sum: = 
SUM(data[value])

Cumulate value sum: = 
VAR _result =
    CALCULATE (
        [Value sum:],
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( 'Date'[Month-Year], 'Date'[Month-Year sort] ),
            ORDERBY ( 'Date'[Month-Year sort], ASC )
        )
    )
RETURN
    IF ( [Value sum:], _result )

Cumulate stage ratio %: = 
VAR _allstagecumulate =
    CALCULATE ( [Cumulate value sum:], REMOVEFILTERS ( stage[stage] ) )
VAR _stagecumulate = [Cumulate value sum:]
RETURN
    DIVIDE ( [Cumulate value sum:], _allstagecumulate )

[jeongkim]

Thanks for detailed advice.

I referred to your pbix and got a good insight that unpivot data in the same column would be the simple solution but thing is my data is fetching the values from different data table to Distinct master table which in not in Query so no idea how to unpivot them. 

e.g. Stage B is coming from GR - 5G SvC master and Stage C is coming from RR LGU SVC RR which are in Query.

Key value of distict master table is unique identifier not getting duplicates and other columns being fetched using Lookupvalue.

In this case, shoud I copy GR and RR query data table necessary date columns and append into one new data table and unpivot?

[jeongkim]

Hi,

How can I create one measure of sum of each values? Stage A B C is sequncial and they have own dates in each columns. 

[Jihwan_Kim]

Hi,

Please provide the sample pbix file's link, and then I can try to look into it.
Or, we can try to connect via virtual meeting to share screen and then we can step further from there.

Thank you.