Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I have a matrix set up in BI with the following format:
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?
Solved! Go to Solution.
I have resolved the issue.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |