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've a dataset which looks as mentioned below:
Level_1 | Level_2 | Value |
A | A_1 | 1 |
A | A_1 | 2 |
A | A_2 | 3 |
A | A_2 | 4 |
A | A_2 | 5 |
B | B_1 | 10 |
B | B_1 | 11 |
B | B_1 | 12 |
B | B_1 | 13 |
B | B_2 | 14 |
B | B_2 | 15 |
B | B_3 | 16 |
B | B_3 | 17 |
B | B_3 | 18 |
I want use the matrix visualization to aggregate the data and select the sum of minimum value at at each hierarchy level. Refer to screen shots below:
The matrix visual shows the minimum value which is at next level of hierarchy, but I need the outcome at "15" which is minimum out of 15 and 126. Similarly if we go a level down:
I need the output as 3 instead.
Any help will be very much appreciated.
Solved! Go to Solution.
Hi @Anonymous
You can try the below DAX expression
Min of Values = VAR levelOne = MINX( ALL( YourTable[Level_1] ), CALCULATE( SUM( YourTable[Value] ) ) ) VAR levelTwo = MINX( ALL( YourTable[Level_2] ), CALCULATE( SUM( YourTable[Value] ) ) ) RETURN SWITCH( TRUE, HASONEVALUE( YourTable[Level_2] ), levelTwo, HASONEVALUE( YourTable[Level_1] ), levelOne, SUM( YourTable[Value] ) )
Hi @Anonymous
You can try the below DAX expression
Min of Values = VAR levelOne = MINX( ALL( YourTable[Level_1] ), CALCULATE( SUM( YourTable[Value] ) ) ) VAR levelTwo = MINX( ALL( YourTable[Level_2] ), CALCULATE( SUM( YourTable[Value] ) ) ) RETURN SWITCH( TRUE, HASONEVALUE( YourTable[Level_2] ), levelTwo, HASONEVALUE( YourTable[Level_1] ), levelOne, SUM( YourTable[Value] ) )
Hi @Mariusz
Is it possible to filter on another column. I have similar set of data where I'm looking to get min % value and roll it up to higher level. In my case I have the seperate level colums and % value but also location.
1.1.2. 50%
1.1.2.1 0 %
1.1.2.2 33%
1.1.2.3. 38%
In my case above, the rollup should be 0 %.
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |