Blank values in Matrix total - Matrix total made to work differently from row totals

[Mwhitnack]

Hi all,

I'm having trouble getting blank values to fill in properly here. 

I have three meaures:

1.)Running Total - This is a running total of the pallet quantites in a warehouse. 

Running Total =

CALCULATE (

SUM (PalletTable[Amount]),

FILTER (

ALL ( 'PalletTable' ),

PalletTable[Date] <= max('Date'[Date])),

VALUES(sqft[Client]),

VALUES(whse[Warehouse]) )

2.)Total Sqft - Disregard this. It was an old formula that didn't work correctly in the Matrix total

3.)New Sqft Total - This is the forumla that multplies the Running Total by the SqftM (Square foot multuplier) to get the total squarefootage occupied in the warehouse. This formula produced two different values depending on whether it's in the total category or just a regular row

Formula:

New Sqft Total =

VAR totalx = SUMMARIZE('PalletTable',

SQFT[Client],

"New", [Total Sqft])

RETURN

IF(HASONEVALUE(SQFT[Client]),[Total Sqft], SUMX(Totalx, [New])

)

For some reason the "New Sqft Total" has blank rows in it. It also doesn't summarize across rows properly. For example on line 13 New Sqft Total under TOTAL is 31,975.19 but so is the New Sqft Total under NORVAN. I'm thinking this is because the value under BLUMON (1,996,67) is carried over from the previous day. Since there isn't a new value there it won't add add it to the row total. This would also explain why there are blank rows.

How would I go about fixing the blank rows/incorrect sums? Let me know if you guys need more information.

[mahoneypat · Accepted solution]

The HASONEVALUE is preventing your total.  You can reference your existing measure in an expression like this, and use this in its place.  I'm not sure if Norvan/Blumon are Clients or Warehouses, but use that column here.

NewMeasure = SUMX(VALUES(sqft[client]), [New Sq Ft Total])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Mwhitnack]

That worked! Thanks!