Measure works at line level, but returns wrong total - Contexts are in separate tables

[Funk-E-Guy]

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)

• Customers
• Locations
• Items
• Dates
• Forecasts
• Sales

These tables are all related to each other like so:

I then have my data in measures;

• FCST QTY = CALCULATE(SUM(Forecasts[Quantity]))
• Sales QTY = CALCULATE(SUM(Sales[Quantity]))
• FCST Discrepancy = [FCST QTY]-[Sales QTY]
• FCST Overage = -min([FCST Discrepancy], blank())
• FCST Shortage = max([FCST Discrepancy], blank())

I am then trying to create a matrix showing:

• heirarchical rows for Customer/Location/Item
• Columns with Month
• Values are my measures

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

[HotChilli]

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

[Funk-E-Guy]

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.

[Funk-E-Guy]

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

[HotChilli]

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 )

[Funk-E-Guy]

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?