cancel
Showing results for
Did you mean:
Frequent Visitor

Matrix calculating sub totals - hard to understand

Calculating subtotals in a matrix remains something difficult to do.

In our work environment, we have to registrate how long different items are available (visible) in an application.

An item can be available multiple times each month for different time periods

So I have table with registrations like this ( item, year, month, availability(hours))

 ITEM YEAR MONTH AVAILABILITY item 1.1 2021 7 0,1 item 1.1 2021 5 11 item 1.2 2021 6 15,5 item 1.3 2021 7 26,1 item 1.1 2021 5 0,9 item 1.2 2021 6 5,6 item 1.3 2021 7 8 item 2.1 2021 5 11 item 2.2 2021 6 15,5 item 2.1 2021 7 26,1 item 2.2 2021 5 0,2 item 2.2 2021 6 9,2

All items belong to a group. We use this table "tableGroupItem" to link item and group.

 GROUP ITEM Group 1 item 1.1 Group 1 item 1.2 Group 1 item 1.3 Group 2 item 2.1 Group 2 item 2.2 Group 2 item 2.3 Group 3 item 3.1 Group 4 item 4.1

I need a report with the average availability for each item per month.

Also totals per group and an overall total.

I try to calculate my report this way

I use a matrix with this formula to get the report below

AverageAvailability =

VAR MyAvgTime = AVERAGEx( tabelRegistration, tabelRegistration [ItemAvailability] )

VAR MyResult =

SWITCH(

TRUE(),

ISINSCOPE(tabelGroupItem[GroupId]) && ISINSCOPE(tabelGroupItem[ItemId]), MyAvgTime,

ISINSCOPE(tabelGroupItem[GroupId]), SUMX( VALUES(tabelGroupItem[ItemId]), MyAvgTime ),

SUMX( VALUES( tabelGroupItem [ItemId] ), MyAvgTime )

)

RETURN MyResult

This is my report. As you can see, the totals are wrong.

27.2 + 26.4 + 8.7 + 31.4 = 93.7     and the report gives me 91.4

What is my mistake?

Thanks a lot for giving me some help or some guidelines to solve this issue.

Koen

1 ACCEPTED SOLUTION
Frequent Visitor

This video has helped me solving the issue.

How To Fix Matrix Totals In Power BI - Bing video

5 REPLIES 5
Frequent Visitor

It would lead us too far to explain exactly what this means.

What the end user wants is to see

• the sum of the averages at group level. So instead of 91.4 I need 93.7 as the result of the sum (27.2 + 26.4 + 8.7 + 31.4)
• the sum of all the group level values as a total
Super User

It would lead me too far to give sample DAX to implement this but I'd suggest that if you want to sum over items averages then you need to take the average over each item separately rather than all together in a single variable.

Frequent Visitor

This video has helped me solving the issue.

How To Fix Matrix Totals In Power BI - Bing video

Super User

A variable is a fixed constant value once it has been defined. Thus

``SUMX ( VALUES ( tabelGroupItem[ItemId] ), MyAvgTime )``

is the same as multiplying MyAvgTime by the number of ItemId values. In the cell pointed out, instead of summing the averages underneath, it's taking the average over the whole of Group 1 and then multiplying that average by 4.

In general, adding up averages isn't terribly meaningful, so I'm hesitant to suggest a solution that does give the result you're expecting. How do you interpret 91.4? What does that number actually tell you?

Frequent Visitor

It would lead us too far to explain exactly what this means.

What the end user wants is to see

• the sum of the averages at group level. So instead of 91.4 I need 93.7 as the result of the sum (27.2 + 26.4 + 8.7 + 31.4)
• the sum of all the group level values as a total

Announcements

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!