Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Trudgeon
Helper III
Helper III

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
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.