Forum Discussion
Sum up minimum value and 0 value
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 |
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.
3 Replies
sunah132 , Try like this once
SUMX ( VALUES ( 'Table'[Date] ),calculate( MIN ( 'Table'[Value] ) ))
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.
