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
Wkeith
Helper II
Helper II

Matrix Row Totals Working but not the Column Totals

Hi, I have a matrix set up in BI with the following format: 

image.png

 

Basically the format is in the "Rows" I have Users, in the "Columns" I have Product Category, and in the "Values" I have Revenue, % of total (which is that products % of total for that users overall revenue), and opportunity (a measure that is causing my totals to have some trouble). The opportunity measure is basically a measure that says if that users % of total for that product is less than the average % of total for that product for all users than generate an opportunity amount that we are missing out on. This is the measure that is causing my row and column subtotals to be incorrect. 

 

The workaround I have implemented so far is based on this article https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/. The DAX I have currently that fixes my row subtotals but not my column subtotals: 

XBOX Opportunity Fixed = if(HASONEVALUE(DimProduct[N_Market_Segment]), [XBOX Opportunity], sumx(values(DimProduct[N_Market_Segment]),[XBOX Opportunity]))

*N_Market_Segment is the product category 

*XBOX Oporuntity is my measure that calculates the opportunity for each user 

 

This code works great to fix my row subtotals (I.E. an individuals users total opportunity) but does not fix my column subtotals (I.E. a product's overall opporutnity). Currently for each product the column subtotal just displays $0 (

(The Grand Total part of the picture above is what I am referring to. It just displays $0 for each products opporutnity amount). 

 

How do I modify my above forumla so that it fixes the column subtoals but also maintains the correct values for the invididual cells and the row subtoals? 

 

 

1 ACCEPTED SOLUTION
Wkeith
Helper II
Helper II

I have resolved the issue. 

View solution in original post

2 REPLIES 2
Wkeith
Helper II
Helper II

I have resolved the issue. 

Wkeith
Helper II
Helper II

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.