Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
71 | |
70 | |
43 | |
31 | |
26 |
User | Count |
---|---|
89 | |
49 | |
44 | |
38 | |
37 |