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.
I have a measure that summarizes if value is greater than 0 (Calc - Tillgänglig) and another that summarize if the value is bellow 0 (Calc - Överallokering)
Calc - Tillgängligt =
VAR Planned =
CALCULATE(SUM('Produktion - Planering'[Värde]))
VAR Target =
CALCULATE(SUM('Employee - Target - TJM'[Target-semester]))
Return
if(Target - Planned>0,
(Target - Planned), 0
)
Calc - Överallokering =
VAR Planned =
CALCULATE(SUM('Produktion - Planering'[Värde]))
VAR Target =
CALCULATE(SUM('Employee - Target - TJM'[Target-semester]))
Return
if(Target - Planned<0,
-(Target - Planned), 0
)
These two measures are set as values in a matrix visual and it shows up correct when I only show the lowest level, the Employee-level.
If I summarize this in a grouped level, it shows a calculated value as the measure on grouplevel but correct on employee level.
Position - Roll | Employee | Summa av Calc - Tillgängligt | Summa av Calc - Överallokering |
Group 1 Summa | 44 | 0 | |
Group 1 | Name 1 | 0 | 60 |
Name 2 | 0 | 40 | |
Name 3 | 0 | 0 | |
Name 4 | 40 | 0 | |
Name 5 | 0 | 0 | |
Name 6 | 0 | 0 | |
Name 7 | 0 | 0 | |
Name 8 | 40 | 0 | |
Name 9 | 0 | 0 | |
Name 10 | 0 | 0 | |
Name 11 | 0 | 0 | |
Name 12 | 24 | 0 | |
Name 13 | 0 | 0 | |
Name 14 | 40 | 0 | |
Name 15 | 0 | 0 | |
Name 16 | 0 | 0 | |
Name 17 | 0 | 0 | |
Group 2 Summa | 230 | 0 | |
Group 2 | Name 18 | 0 | 40 |
Name 19 | 0 | 40 | |
Name 20 | 40 | 0 | |
Name 21 | 40 | 0 | |
Name 22 | 0 | 0 | |
Name 23 | 0 | 0 | |
Name 24 | 40 | 0 | |
Name 25 | 0 | 0 | |
Name 26 | 30 | 0 | |
Name 27 | 0 | 0 | |
Name 28 | 0 | 0 | |
Name 29 | 40 | 0 | |
Name 30 | 0 | 0 | |
Name 31 | 40 | 0 | |
Name 32 | 40 | 0 | |
Name 33 | 0 | 0 | |
Name 34 | 0 | 0 | |
Name 35 | 0 | 0 | |
Name 36 | 0 | 0 | |
Name 37 | 0 | 0 | |
Name 38 | 0 | 0 | |
Name 39 | 40 | 0 | |
Name 40 | 0 | 0 | |
Name 41 | 0 | 0 | |
Totalsumma | 274 | 0 |
But I want it to show the col-sum in the subtotal and not as shown above where it calculates the value.
in other words, I would like it to show values like this.
Position - Roll | Employee | Summa av Calc - Tillgängligt | Summa av Calc - Överallokering |
Group 1 Summa | 144 | 100 | |
Group 1 | Name 1 | 0 | 60 |
Name 2 | 0 | 40 | |
Name 3 | 0 | 0 | |
Name 4 | 40 | 0 | |
Name 5 | 0 | 0 | |
Name 6 | 0 | 0 | |
Name 7 | 0 | 0 | |
Name 8 | 40 | 0 | |
Name 9 | 0 | 0 | |
Name 10 | 0 | 0 | |
Name 11 | 0 | 0 | |
Name 12 | 24 | 0 | |
Name 13 | 0 | 0 | |
Name 14 | 40 | 0 | |
Name 15 | 0 | 0 | |
Name 16 | 0 | 0 | |
Name 17 | 0 | 0 | |
Group 2 Summa | 310 | 80 | |
Group 2 | Name 18 | 0 | 40 |
Name 19 | 0 | 40 | |
Name 20 | 40 | 0 | |
Name 21 | 40 | 0 | |
Name 22 | 0 | 0 | |
Name 23 | 0 | 0 | |
Name 24 | 40 | 0 | |
Name 25 | 0 | 0 | |
Name 26 | 30 | 0 | |
Name 27 | 0 | 0 | |
Name 28 | 0 | 0 | |
Name 29 | 40 | 0 | |
Name 30 | 0 | 0 | |
Name 31 | 40 | 0 | |
Name 32 | 40 | 0 | |
Name 33 | 0 | 0 | |
Name 34 | 0 | 0 | |
Name 35 | 0 | 0 | |
Name 36 | 0 | 0 | |
Name 37 | 0 | 0 | |
Name 38 | 0 | 0 | |
Name 39 | 40 | 0 | |
Name 40 | 0 | 0 | |
Name 41 | 0 | 0 | |
Totalsumma | 454 | 180 |
Is there a workaround to achive this? When it calculates like this I can not use a better visual to show the values in a subtotal-level.
Thanks in advance!
//Tommy
Solved! Go to Solution.
I did not manage to get the second measure work the way I wanted so I created a new table in Power Query were each column represents a measure. Then the sum are correct on my subtotals.
Hi @TommyMossberg -
Instead of putting the two measures directly into the matrix, add a second measure for each that does the subtotals correctly
Tillganglig_Display =
IF (
HASONEVALUE ( STotal_Tab[Employee] ),
[Tillganglig],
SUMX ( STotal_Tab, [Tillganglig] )
)
Hope this helps
David
I did not manage to get the second measure work the way I wanted so I created a new table in Power Query were each column represents a measure. Then the sum are correct on my subtotals.
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 |
---|---|
45 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |