Denormalised tables and grid totals

[RudyL]

Hi,

Let me give an example of my PowerBI grid struggles.

I have this denormalised table (see below) containing a list of workstations in 2 buildings having 2 floors each. For each floor I have the office space area as well. 

First simple grid I want to add to my report is a list with a column for building, floor, sqm and number of workstations. I can just add columns building and floor and also add workstation defined as a count to get a total per floor and a grand total below the list. The SQM is trickier. I can add the sqm column and not summarize. This results in the correct area per floor, but now I have no grand total.

Q1: how can I show sqm per floor and have a grand total as well?

Extra challenge: I add a measure sqmws=divide(max(floorsqm),count(workstation),0). This returns the correct value of sqm per workstation on a row level in my grid of floors. The grand total result is way off, which is obvious because I use a max in my measure.

Q2: How can I fix this and have correct sqm per workstation on a row level AND as a grand total.

Extra Extra challenge: Now I want to remove the floor column from my grid and have the same other  results (building, sqm per building, number of workstations and sqm per workstation) but now on a building level. The trick with the don’t summarize for the sqm field doesn’t work anymore. Count of workstations is still OK, but sqm per workstation is a mess.

Q3: how can I have a simple aggregated grid that has summarized values per building on a row level and the correct grand totals?

Hope you girls and guys might be able to help,

Rudy

[Greg_Deckler · Accepted solution]

@RudyL This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[RudyL]

@Greg_Deckler ,

at first I was a bit disappointed my struggles seem nothing more than common problems 🙄.

But especially the second link you added as a solution is exactly what I was looking for. Thanks for your time and wisdom. 

[Greg_Deckler]

@RudyL Unfortunately measure totals have been a thorn in the side of DAX/Power BI since it's inception. No big deal that it is an old issue, it is still new to people learning DAX and Power BI!