The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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] ) ))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
92 | |
90 | |
72 | |
69 |
User | Count |
---|---|
232 | |
128 | |
117 | |
82 | |
82 |