Totals for a Matrix

[Anonymous]

Hello All,

Using a Matrix for the first time and I am trying to understand the totals

I would expect this total to show 221,507 for the Column 'FYTD Operating Hours - Budget', but it does not.

Any help is much appreciated

@edhans 

[v-shex-msft]

Hi @Anonymous,

Please take a look at following measure total issues summary blog if it helps:

Measure Totals, The Final Word 

These formulas work with general multiple aggregate functions or total level calculations. For complex conditions or nested measure calculations, you still need to coding formula and manually group these different records with multiple aggregation functions. 

Regards,

Xiaoxin Sheng

[Anonymous]

I am really having trouble undetstanding that post, I am still kind of new to Power BI, could you look at the file and tell me exactly what measure I should write to get the correct totals?

Here is a link to the file, we are a state funded instituition so all of this info is available to the public, nothing is sensitive

https://www.dropbox.com/s/c3gzaz1998gtyzp/IS%20Salaries%20Mock%20up.pbix?dl=0

[edhans]

You've blanked out so much of the image I am having trouble seeing what the hierarchy is. However, totals in tables and matrix visuals do not total the amounts above. 

Instead, the measure you used for the Budget column is re-calculated at the bottom with no filters for whatever fields you have there. So it isn't saying, give me "Police" plus "Fire" plus "EMT" or whatever your fields are, it is saying "give me the total ignoring filters for that column that this matrix provides."

It honors filters from external sources, like the Filter Pane or Slicers, and even Crossfiltering.

Please provide some data and your measure so we can assist.

This article gives a good explanation as what is going on though. but holy cow, it gives some bad solutions. @Greg_Deckler has a good link, but I cannot find it. Maybe he will post it here and then I will bookmark it forever.

[Anonymous]

I am really having trouble undetstanding why it just shows the total for one row though, the 20,020 number for example

Here is a link to the file, we are a state funded instituition so all of this info is available to the public, nothing is sensitive

https://www.dropbox.com/s/c3gzaz1998gtyzp/IS%20Salaries%20Mock%20up.pbix?dl=0

@edhans 

[edhans]

Here is the problem.

Total Salaries - Budget = [Average Hourly Rate - Budget]*[FYTD Operating Hours - Budget]

For an individual line, that formula makes sense. average rate * budget hours.

But for a total, it isn't doing the average rate * hours for each line and adding them up. It is adding up all of the averate rates, then adding up all of the hours, then doing the multiplication, and it is doing context transtion to boot since those are both measures. See below for more on this.

I'd need time to go through this to fix. The issue is you are using measures within measures within measures, and you are not using proper syntax so it is impossible to tell what is a measure and what is a column. When referencing a measure, you never include the table name, so [Total Sales] is a measure. When referencing a column, you always include the table name. So Budget[Avg rate].

So in this measure:

FYTD Operating Hours - Budget = 
VAR DeptID = MAX(Budget[Department ID])
RETURN
SUMX(
    FILTER(
        Budget,
        [Department ID] = DeptID
    ),
    [Operating FTEs]
)*2080/12*[FY Month]

Budget[Department ID] is a column

[Department ID] looks like it is a measure, but it isn't.

[Operating FTEs] looks like it is a measure, but it isn't.

[FY Month] looks like it is a measure, and it is.

I'd need to go through and clean all of that up to get started. For example:

FYTD Operating Hours - Budget =
VAR DeptID =
    MAX( Budget[Department ID] )
RETURN
    SUMX(
        FILTER(
            Budget,
            Budget[Department ID] = DeptID
        ),
        Budget[Operating FTEs]
    ) * 2080 / 12 * [FY Month]

Then I can clearly read what is going on.

Then the work begins. When you use a measure in another measure, you are forcing an implicit calculate around it. So in the last part of your measure above, 

*2080/12*[FY Month]

It is really:

*2080/12*CALCULATE([FY Month])

Which forces a context transition which is hands down the hardest thing in DAX to learn and wrap your head around, which is why I avoid using measures within other measures. 

Unfortunately, Excel taught us to create a forumla in one cell and use it in another by referencing cell A1 with the original formula. In DAX, that can have adverse consequences, so I more often than not reuse code in measures as opposed to using measures inside of other measures unless I specifically need context transition to occur. This is very common using ADDCOLUMNS() for example.

Maybe someone else will jump in with a faster solution. I will look at this later today though.

[Anonymous]

Thank you @edhans , you are litteraly the best

Thank you for taking the time to look this over later and clean up, I am learning a lot from you

[edhans]

No prob. Glad to help. Wanted to be 100% clear I am not being critical. We all have to learn these "rules." I am taking the SQLBI courses myself right now. If you can get your boss to spring for them, learinng from "The Italians" is the best way to do this. They are Phenominal!

[Anonymous]

Awesome! I will check this out

[edhans]

I was looking at this at lunch and this just got an order of magnitude more complex. You have a many-to-many bi-directional filter. That should almost never be used. Not never, but almost never. Both are advanced modeling concepts and should be avoided by remodling you data if possible. You can often get rid of many-to-many using a bridge table, and bi-directional can often be activated on a measure by measure basis using CROSSFILTER() inside of a CALCULATE(), but without enabling on the model.

I'm going to step away from this at this point as this is, for me, a few hrs work to understand and remodel the data, and that gets into a consulting gig vs a community help forum. So the help I can offere is remodel the data and see if you can clean it up with neither bi-directional filtering nor many-to-many relationships.  You can see below even MS recommends against general usage of M2M.

Microsoft Guidance on Many-To-Many Relationships

[Anonymous]

Sorry to ask another question,

 I just noticed this column 'Total Salaries - Actuals' is not calcualting properly

It should be 14,931.73 X 22.45 = 335,217.34

Thanks again for all of your help @edhans 

[edhans]

The measure doesn't make sense to me. Those are both measures inside that, so no table names should be used. So your formula is:

Total Salaries - Actuals =
SUMX(
    'Operating Hours - Actuals',
    [FYTD Operating Hours]
) * [Average Hourly Rate - Actuals]

WHICH IS ACTUALLY

Total Salaries - Actuals =
SUMX(
    'Operating Hours - Actuals',
    CALCULATE(
        [FYTD Operating Hours]
    )
)
    * CALCULATE(
        [Average Hourly Rate - Actuals]
    )

Because using a measure in a measure wraps it in CALCULATE, which invokes context transition, and the formulas are operating on the BUDGET and CURRENT POSITIONS tables, which are joined by a many-to-many bi-directional filter. So without cracking open DAX Studio and spending a lot of time with the data, I have no idea what that measure is doing.

[Anonymous]

No problem, thank you for looking at it

[Anonymous]

I understand, no problem