Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I'm working on this power BI Matrix and not sure about this step forward. I have brought minimum values of each region's rate based on their dates. I used the formula of
SWITCH (
TRUE (),
ISINSCOPE ( 'Table'[item] ), SUMX ( VALUES ( 'Table'[Date] ), MIN ( 'Table'[Value] ) ),
SUM ( 'Table'[Value] )
)
to get the total rates. The problem I'm facing is that when there's no rate on days for example of 6/5 on Austin, it counts as 0 thus the total becomes 0. Could you please see how this formula can skip the 0 values and still summarize the total?
Thank you very much.
Region | 1-Jun | 2-Jun | 3-Jun | 4-Jun | 5-Jun | Expected Total | Current Matrix |
Austin | 10 | 10 | 10 | 10 | 0 | 40 | 0 |
Dallas | 0 | 25 | 25 | 25 | 25 | 100 | 0 |
Houston | 20 | 20 | 20 | 20 | 0 | 80 | 0 |
San Antonio | 0 | 20 | 20 | 20 | 20 | 80 | 0 |
Total | 30 | 75 | 75 | 75 | 45 | 300 |
Solved! Go to Solution.
Hi @sunah132
You could try this measure:
Measure_Value =
IF (
ISINSCOPE ( 'Table'[Route] ) && ISINSCOPE ( 'Table'[Date] ),
MIN ( 'Table'[Value] ),
SUM ( 'Table'[Value] )
)
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Thank you for the suggestion!
I tried on your suggestion and I should have mentioned this but there are multiple routes under each region. When I applied on yours, it took the sum including 0 but wasn't able to show the route rate at drill down.
Region | 1-Jun | 2-Jun | 3-Jun | 4-Jun | 5-Jun | Total |
Austin | 10 | 10 | 10 | 10 | 40 | |
1 | 2 | 2 | 2 | 2 | 8 | |
2 | 3 | 3 | 3 | 3 | 12 | |
3 | 3 | 3 | 3 | 3 | 12 | |
4 | 2 | 2 | 2 | 2 | 8 |
Hi @sunah132
You could try this measure:
Measure_Value =
IF (
ISINSCOPE ( 'Table'[Route] ) && ISINSCOPE ( 'Table'[Date] ),
MIN ( 'Table'[Value] ),
SUM ( 'Table'[Value] )
)
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
@sunah132 , Try like this once
SUMX ( VALUES ( 'Table'[Date] ),calculate( MIN ( 'Table'[Value] ) ))