Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone, I have a similar but distinct issue from this post; Measure works at line level but returns wrong result on total , the difference being their issue appears consolidated to one table, so therefore the provided SUMX works on the one table. Mine, however, has its data segregated into 6 different tables, and the "context" of my calculations are in 3 of those tables (in bold underline)
These tables are all related to each other like so:
I then have my data in measures;
I am then trying to create a matrix showing:
Per my screenshot above, I am expecting the "FCST Overage" and "FCST Shortage" measures to sum up the values calculated at the line-level based on my heirarchy of rows (Customer / Location / Item), but they are simply using the totals row. And as clarification; I do not want the date included in my calculation context (so that we can look at overages/shortages Monthly/Quarterly, Yearly, etc).
Here's my test model: https://drive.google.com/file/d/1xxp8l_27BVKWtoJh5KsDmJkN1OSLarZX/view?usp=sharing
I don't have the volume to test but you could try a HASONEVALUE with an IF statement so that the SUMMARIZECOLUMNS is only done on the totals row and the [FCST overage]
measure on the 'ordinary' rows
I figured out the formula, however it appears that it's still affected by the volume issue (at least in my case), so I will need to go a different route it seems.
For thoroughness and documentation, here's the formula I created;
FCST Overage 2 =
if(
not(
or(
or(
HASONEVALUE(Customers[Customer ID]),
HASONEVALUE(Locations[Location ID])
), HASONEVALUE(Items[Item ID])
)
),
SUMX(
SUMMARIZECOLUMNS(
Customers[Customer ID],
Locations[Location ID],
Items[Item ID]
),
-min([FCST QTY]-[Sales QTY],blank())
),
[FCST Overage]
)
As explanation, in order to capture whether or not the row is a grand total, we have to NOR (not-or) the three separate HASONEVALUE functions. If NOR returns TRUE (all 3 columns have more than 1 value), then I SUMX, otherwise use the cheap measure.
Unfortunately in my case that doesn't seem to move the needle. I think even just calculating it once is too heavy. It's turning a 5,000×50×20,000×500 computation into a 5,000×50×20,000×1 computation.
HASONEVALUE only seems to accept one column, so how would you recommend I draft that IF statement considering I have 3 separate tables to reference as context?
I've tried this; but the totals are still showing the same incorrect value.
FCST Overage =
if(
and(
and(
HASONEVALUE(Customers[Customer ID]),
HASONEVALUE(Locations[Location ID])
),
HASONEVALUE(Items[Item ID])
),
SUMX(
SUMMARIZECOLUMNS(Customers[Customer ID], Locations[Location ID], Items[Item ID]), [FCST Shortage]
),
[FCST Overage]
)
My preferred method for these issues is to iterate over the rows involved. Sometimes it gets more complicated to get the correct granularity, so for this, I think you have to create a measure like:
SUMX ( SUMMARIZECOLUMNS ( Customers[Customer Name], Locations[Location Name] , Items[Item Name]), [FCST Overage])
the other one will be similar.
Also, I don't think you need the CALCULATE on 2 of the base measures. (FCST QTY, Sales QTY )
Thanks for the reply. I can confirm that this solution does technically work, however from what I can tell this seems to explode in computation time when I plug it into my main model. I assume it's because it's creating a virtual table of Customers × Locations × Items for every single row in my matrix (which is fine for 2×2×2×16 on the test model, but infeasible for 5,000×50×20,000×5,000).
Is there a way to optimize this for larger data sets?
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |