Possible to have matrix with SUMX function in which grand total changes based on drill down level?

[Trudgeon]

I'm working on a matrix report that calculates price variance.  Due to the nature of the calculation, I need to use the SUMX function as the total would not otherwise foot.  Now here is the tricky part...

I want to have 3 drill down levels:  Account, Product Group and Product

I want to have one column for price variance, then give users the ability to drill-down, seeing a different grand total depending on the current drill level.  Below, I'm showing how each drill level would have a different grand total.

Here are the DAX measures based on grouping level...

VARIANCE_PRICE_VAR_SUMX_ACCOUNT_VS_BDGT =

SUMX( VALUES(VIEW_VARIABLE_COSTS_UNION[CUSTOMER_DESC_SOLD]),

VIEW_VARIABLE_COSTS_UNION[VARIANCE_PRICE_VAR_VS_BDGT])

VARIANCE_PRICE_VAR_SUMX_PRODUCT_GROUP_VS_BDGT = SUMX(VALUES(VIEW_VARIABLE_COSTS_UNION[PRODUCT_GROUP]),

VIEW_VARIABLE_COSTS_UNION[VARIANCE_PRICE_VAR_VS_BDGT])

VARIANCE_PRICE_VAR_SUMX_PRODUCT_VS_BDGT = SUMX(VALUES(VIEW_VARIABLE_COSTS_UNION[MFC_PRODUCT]),

VIEW_VARIABLE_COSTS_UNION[VARIANCE_PRICE_VAR_VS_BDGT])

Any help would be appreciated!

[Ashish_Mathur]

Hi,

Does this measure work?

=IF(HASONEVALUE(VIEW_VARIABLE_COSTS_UNION[MFC_PRODUCT]),SUM(VIEW_VARIABLE_COSTS_UNION[VARIANCE_PRICE_VAR_VS_BDGT]),IF(HASONEVALUE(VIEW_VARIABLE_COSTS_UNION[PRODUCT_Group]),Measure3,IF(HASONEVALUE(VIEW_VARIABLE_COSTS_UNION[CUSTOMER_DESC_SOLD]),Measure2,Measure1)))

[Trudgeon]

Thanks for the response.  The formula didn't like the "SUM" expression, so I tried replacing it with CALCULATE but, unfortunately, the totals did not change while drilling.

I also tried the following measure, but it just resulted in the totals all being blank.

VARIANCE_PRICE_VAR =

IF(HASONEVALUE(VIEW_VARIABLE_COSTS_UNION[MFC_PRODUCT]),[VARIANCE_PRICE_VAR_SUMX_PRODUCT_VS_BDGT],

IF(HASONEVALUE(VIEW_VARIABLE_COSTS_UNION[PRODUCT_Group]),[VARIANCE_PRICE_VAR_SUMX_PRODUCT_GROUP_VS_BDGT],

IF(HASONEVALUE(VIEW_VARIABLE_COSTS_UNION[CUSTOMER_DESC_SOLD]),[VARIANCE_PRICE_VAR_SUMX_ACCOUNT_VS_BDGT],

BLANK()

)))