cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Koen_Zamia
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

Koen_Zamia_0-1638202332073.png

 

What is my mistake?

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

 

Koen

 

1 ACCEPTED SOLUTION

5 REPLIES 5
Koen_Zamia
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

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.

This video has helped me solving the issue.

How To Fix Matrix Totals In Power BI - Bing video

AlexisOlson
Super User
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?

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.