cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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.

 

Mwhitnack_0-1598393200147.png

 

 

1 ACCEPTED SOLUTION
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

That worked! Thanks!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors