Running Total based on calculated measure which is Average

[Kalaivani]

Thanks in advance for your help!!

I have a simple measure - PivotWrvus = SUM('ProductivityMeasures'[WRVUs]).

But when I try to do running total based on 2 methods below the var RunningTotal2 is working perfectly based on what am selecting. But RunningTotal1 is giving wrong values. I think it is giving me the overall sum. How to do running total for a calculated measure?

VAR RunningTotal1 =  

    CALCULATE(

        SUMX(

            FILTER(

                 ALLSELECTED('CalendarTable'),

                'CalendarTable'[YearMonthDate] <= MAX('CalendarTable'[YearMonthDate])

        ),

            [PivotWrvus]

        )

    )

VAR RunningTotal2 =

CALCULATE(

        SUM('ProductivityMeasures'[WRVUs]),

        FILTER(

            ALLSELECTED('CalendarTable'),

            'CalendarTable'[YearMonthDate] <= MAX('CalendarTable'[YearMonthDate])

        )

    )

Here is what am getting in both runningtotal calculation.

It's because i have a calulcated measure like this, i want to do running total for t he below calculated measure. So I did a simple example on wrvus but it's not working for me. 

Calculated measure that I have to use for running total:

WrvusServiceLineAvg =

 VAR ServiceLineAvg =

 IF(

    (

        COUNTROWS(ALLSELECTED('Comp Providers'[ProviderName])) > 0 &&

        COUNTROWS(ALLSELECTED('Comp Providers'[ProviderName])) <> COUNTROWS(ALL('Comp Providers'[ProviderName]))

    ) ||

    (

        COUNTROWS(ALLSELECTED('Comp Providers'[Expertise])) > 0 &&

        COUNTROWS(ALLSELECTED('Comp Providers'[Expertise])) <> COUNTROWS(ALL('Comp Providers'[Expertise]))

    ) ||

    (

        COUNTROWS(ALLSELECTED('Comp Providers'[Clinic Manager])) > 0 &&

        COUNTROWS(ALLSELECTED('Comp Providers'[Clinic Manager])) <> COUNTROWS(ALL('Comp Providers'[Clinic Manager]))

    ) ||

    (

        COUNTROWS(ALLSELECTED('Comp Providers'[AACMO])) > 0 &&

        COUNTROWS(ALLSELECTED('Comp Providers'[AACMO])) <> COUNTROWS(ALL('Comp Providers'[AACMO]))

    ) ||

    (

        COUNTROWS(ALLSELECTED('Comp Providers'[VP])) > 0 &&

        COUNTROWS(ALLSELECTED('Comp Providers'[VP])) <> COUNTROWS(ALL('Comp Providers'[VP]))

    )

    ||

    (

        COUNTROWS(ALLSELECTED('Comp Providers'[DCO])) > 0 &&

        COUNTROWS(ALLSELECTED('Comp Providers'[DCO])) <> COUNTROWS(ALL('Comp Providers'[DCO]))

    )||

    (

        COUNTROWS(ALLSELECTED('Comp Providers'[Org Code])) > 0 &&

        COUNTROWS(ALLSELECTED('Comp Providers'[Org Code])) <> COUNTROWS(ALL('Comp Providers'[Org Code]))

    ),

    CALCULATE(

        'ProductivityMeasures'[PivotAvgWrvus],

        FILTER(

            ALL('Comp Providers'[ProviderName],'Comp Providers'[Expertise],'Comp Providers'[AACMO],'Comp Providers'[Clinic Manager],'Comp Providers'[DCO],'Comp Providers'[VP],'Comp Providers'[Org Code]),

            TRUE()   -- This ensures all providers are included in the filter context

        ),

        VALUES('Comp Providers'[ServiceLine])  -- Keeps the ServiceLine filter context intact

    ),

    BLANK()  -- Return blank if the condition is not met

)

My calculate measure that I need to

[MFelix · Accepted solution]

Hi @Kalaivani ,

When you do a sumx you are calculating the sum of all values in a table this means that you are calculating the values for al the data in YearMonthDate column, can be incorrect on my analysis but this seems to be a date value in order to get the sumx to work properly you have to do an aggregation on Month something similar to this:

VAR RunningTotal1 =  
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'CalendarTable' ),
            'CalendarTable'[YearMonthDate] <= MAX ( 'CalendarTable'[YearMonthDate] )
        ),
        'CalendarTable'[Year-Month],
        "PivotValue", [PivotWrvus]
    ),
    [PivotValue]
)

[Kalaivani]

Hi @MFelix  Thanks so so much for your response. I tried your solution and it worked perfectly for me!