cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Trudgeon Regular Visitor
Regular Visitor

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

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.

 

Drill Down Levels.JPG

 

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!

 

 

 

2 REPLIES 2
Super User
Super User

Re: Possible to have matrix with SUMX function in which grand total changes based on drill down leve

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 Regular Visitor
Regular Visitor

Re: Possible to have matrix with SUMX function in which grand total changes based on drill down leve

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()
)))