Dynamic Measure + Prior Year Avg/Sum

[SimonNU]

The below is a dynamic measure (using a disconnected table) for measures [measure 1], [measure 2], [measure 3] ... [measure n] which is wrapped inside a prior year calculation.

Unfortunately, [measure 3] actually requires an AVERAGEX as opposed to SUMX. Can anyone think of an elegant way to re-arrange this to account for this adjustment?

[COLOR=#FFFFFF][FONT=Consolas]Dynamic PY CSC :=[/FONT][/COLOR]
Dynamic Measure - PY :=
IF (
    HASONEVALUE ( 'Dynamic Measures'[ID] ),
    SUMX (
        VALUES ( 'Time Filters'[YearPeriodNumber] ),
        CALCULATE (
            SWITCH (
                VALUES ( 'Dynamic Measures'[ID] ),
                1, [measure 1],
                2, [measure 2],
                3, [measure 3]
            ),
            ALL ( 'Time Filters' ),
            FILTER (
                ALL ( 'Time Filters'[YearPeriodNumber] ),
                'Time Filters'[YearPeriodNumber]
                    = EARLIER ( 'Time Filters'[YearPeriodNumber] ) - 12
            )
        )
    )
)

 

[scottsen]

Hmm... so, this looks a bit weird to me. In that, I would expect you to have a [Dynamic Measure] already, and for this PY version to just "use" that.

=CALCULATE([Dynamic Measure], ALL( 'Time Filters' ), 'Time Filters'[YearPeriodNumber] = VALUES('Time Filters'[YearPeriodNumber]) - 12)

Or ... something like that. Am I cray cray?

 

[SimonNU]

You're correct, [Dynamic Measure] exists and I do use it in the measure normally. I expanded it to show how SUMX negatively effects a dynamic measure that is an average (it sums up the averages for the selected periods - not good!). In hindsight I probably shouldn't have

I like your attempt as using calculate to solve the issue. Unfortunately it appears to only cater for scalar values - it bugged when I selected more than 1 period.

The actual formula is:

Dynamic PY Measure :=IF (
    HASONEVALUE ( 'Dynamic Measures'[ID] ),
    SUMX (
        VALUES ( 'Time Filters'[YearPeriodNumber] ),
        CALCULATE (
            [Dynamic Measure],
            ALL ( 'Time Filters' ),
            FILTER (
                ALL ( 'Time Filters'[YearPeriodNumber] ),
                'Time Filters'[YearPeriodNumber]
                    = EARLIER ( 'Time Filters'[YearPeriodNumber] ) - 12
            )
        )
    )
)

 

[scottsen]

I think I am getting thrown off by weird measure naming or something, I will try and ignore that... and just read your words

So... you have a situation where sometimes you want a SUMX and sometimes you want an AVERAGEX. I have a ... niggle... in the back of my head. AVERAGEX is secretly SUMX/COUNTROWS() maybe? Can we always SUMX, but based on the measure selected... sometimes divide by the countrows? Like... divide by COUNTROWS(Table) - [Zero or One from Measure Selected] * COUNTROWS(Table) ... so that you divide by COUNTROWS()-COUNTROWS() or... 1, or you divide by COUNTROWS()-0*COUNTROWS() ... which gives the AVERAGE?

I dunno, its 11:30pm after a long day of DAX and C#, I'm hoping my niggle of an idea gives you an actual idea...?