Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
This video has helped me solving the issue.
It would lead us too far to explain exactly what this means.
What the end user wants is to see
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |