Sum up minimum value and 0 value

[sunah132]

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

[sunah132]

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

[v-jingzhang · Accepted 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.

[amitchandak]

@sunah132 , Try like this once

SUMX ( VALUES ( 'Table'[Date] ),calculate( MIN ( 'Table'[Value] ) ))