How to simply sum up rows without wrong subtotals calculated

[cheeyayee]

We had measures of “Price Variance” which calculated as:

IMS Price Variance = SWITCH(TRUE(),[IMS Price CY]<=0,0,[IMS Price LY]<=0,0,[Quantity Net LY]<=0,0,('In-Market Sales'[IMS Price CY]-[IMS Price LY])*[Quantity Net LY])

So that we get variance as [IMS Price CY]-[IMS Price LY])*[Quantity Net LY] only if all variables are above 0, otherwise just show 0. 

However, for the subtotal it uses the formula to calculate on the last row again, where we just want simple sum of all the price variance showing above.

How to fix this using only one measure with the correct subtotal?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try something like below.

IMS Price Variance =
SUMX (
    VALUES ( 'tablename'[DISTRIBUTION_CHANNEL] ),
    CALCULATE (
        SWITCH (
            TRUE (),
            [IMS Price CY] <= 0, 0,
            [IMS Price LY] <= 0, 0,
            [Quantity Net LY] <= 0, 0,
            ( [IMS Price CY] - [IMS Price LY] ) * [Quantity Net LY]
        )
    )
)