Matrix calculating sub totals - hard to understand

[Koen_Zamia]

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

[Koen_Zamia]

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

[AlexisOlson]

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.

[Koen_Zamia · Accepted solution]

This video has helped me solving the issue.

How To Fix Matrix Totals In Power BI - Bing video

[AlexisOlson]

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?

[Koen_Zamia]

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