How to calculate an average of sums

[Kashuo]

Hello,

I have multiple buildings. Each building has multiple offices. Each office contains desks.

Each row of my table is an office. Each office has a desk count. The desk counts for each office range from 1 desk to over 200. I have created a set of groups to put segmented ranges of offices into buckets according to their desk count. Here are the buckets:

When I create a bar chart to visualize numbers of offices with various desk counts, I get this:

That chart show that the building in question has 41 ofices with either 1 or 2 desks in them, 76 offices with 3 or 4 desks in them, and so on. Here's a second building:

This building has a different breakdown. It has 93 offices with 1 or 2 desks, 318 offices with 3 or 4 desks, and so on. When I select to include both buildings, it sums up the desk count totals so that I get this:

As you can see, it gives me the total desk count as a sum. This is not what I want, though, I want an average. However, when I change the drop down on the Value from Sum to Average, I don't get 67 and 159 and so on (the average of the total desk count for that room type per building)). I get the average of the individual desk counts themselves, like this:

I've tried using some DAX formulas to try to get the initial SUM per building to stick, and then divide that by number of rows, but it just keep reverting to the individual desk count. It seems like I want to use the "Don't Summarize" option but it's not available.

Any ideas? Thanks!!

[dkay84_PowerBI]

My guess is that you would need to use IF(HASONEVALUE()) to determine if one building is selected, in which case perform the SUM() function, otherwise perform an AVERAGE().  Without any sample data or your PBIX file, we can't play with the syntax to see what works.  Can you share anything?

[Kashuo]

Thanks, I could possibly share something, but first let me clarify a bit based on what you've written so far.

This issue is not that SUM isn't working. SUM always works. 

The issue is that AVERAGE doesn't work the way I need it to. It's averaging the individual desk counts of the rooms, rather than the desk counts for that room type bucket between buildings. Does that make sense?

[dkay84_PowerBI]

Think about it this way.  All aggregations like SUM, AVERAGE etc look at all the records in the table/column that they are operating on.  If there is a filter applied (within the measure or from a slicer), these functions operate on the filtered table.

In your example, you have grouped the data into buckets.  When you select one building, the table returned has the records for just that building.  However, when you select 2+ buildings, the table contains records for those buildings.  So, after selecting multiple buildings, your table will have values for multiple buildings in each bucket.  Thus, the AVERAGE function will average those values (think records in your table), not average the sum of the different buildings.

The pseudo code I suggested was just to use an if/then statement that checks if one building is selected to perform SUM and if multiple are selected to perform the necessary AVERAGE.  However, literally using AVERAGE() may not work.  You may need to do a count of buildings selected to divide the desk count and/or create other intermediate measures.  That is why I asked if you could share some sample data/pbix.

[Kashuo]

Okay, check this out: https://drive.google.com/drive/folders/0B9wY6DHTb6A4Sjd3alFwV1VLaGs?usp=sharing

[dkay84_PowerBI]

How many buildings do you have (or will have in the future)?

[Kashuo]

For this particular application I will be comparing 5 at a time, but we have over 100 and growing. I may need to compare more than 5, or possibly all at once for a similar comparison.

[dkay84_PowerBI]

So this is a tough one.  I asked about the number of buildings to determine if it was realistic to build and maintain a data model that could perform the desk count grouping for every building individually.  Doesn't sound like that is the way to go here.

I will keep looking but I'm not sure how to pass the filter context to the measure(s) so that it knows if you have selected, for example, building A and C, that it needs to sum building A and C (separately) and then average those numbers.

[Kashuo]

Hi, any luck? Several people from my organization have tried to solve this to no avail. If you crack it you will be a hero!

[LaurentCouartou]

Hi,

I am not certain I understand what you mean with "Average" here.

Do you mean the number of desks divided by a the number of offices for the currently selected buildings and the current range?

In other words, what should be the denominator?

[Kashuo]

The denominator should be the number of buildings that I am comparing to each other. But based on a multi-choice slicer, I want to select a variety of buildings, not always the same number of buildings. If you download the file I shared in my original post, it should make more sense.

In other words both of the following scenarios should work:

Scenario 1:

Bldg A: 10 desks in 1 or 2 person offices

Bldg B: 20 desks in 1 or 2 person offices

Average number of desks across our fleet of buildings being compared in all 1 or 2 person offices: (10 + 20)/2 = 15

Scenario 2:

Bldg A: 10 desks in 1 or 2 person offices

Bldg B: 20 desks in 1 or 2 person offices

Bldg C: 30 desks in 1 or 2 person offices

Average number of desks across our fleet of buildings being compared in all 1 or 2 person offices: (10 + 20 + 30)/3 = 20

[LaurentCouartou · Accepted solution]

Assuming you have all your data in one table, the formula should look like this:

Measure =
       DIVIDE( SUM(YourTable[NrOfDesks])
          , DISTINCTCOUNT( YourTable[YourBuldingIdentifier])
       )

DIVIDE is juste a safe division (handles division by 0).

DISTINCTCOUNT( YourTable[YourBuldingIdentifier] ) will return the (distinct) number of buildings. This assumes you have one column in your table identifying each building.

[Kashuo]

This worked! Thank you so much.