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.