Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello guya,
Hope you all are safe,
I have a requirment where I need to calculate sum of quantity based on certain filter and condition.
conditions are:
1. if Type = "inf" then only calculate sum when con_type like %A% else sum(quantity) of type.
2.sum( booked quantity) should be greater than 0 when export underlying data
I have tried a measure but it filtering everything for con_type like %A% which is wrong, I want only "inf" to get filter notthing else i.e.
BQ test =
VAR inf =
CALCULATE (
SUM ( Bookings[QUANTITY] ),
Bookings[Type] = "inf"
)
VAR qty =
IF (
inf,
(
CALCULATE (
SUM ( Bookings[QUANTITY] ),
FILTER (
Bookings,
Bookings[Con_type] = "{A}-{N}"
|| Bookings[Con_type] = "{A}"
)
)
),
CALCULATE ( SUM ( Bookings[QUANTITY] ) )
)
VAR qty2 =
IF (
inf,
(
CALCULATE (
SUM ( Bookings[QUANTITY] ),
FILTER ( Bookings, Bookings[QUANTITY] > 0 ),
FILTER (
Bookings,
Bookings[Con_type] = "{A}-{N}"
|| Bookings[Con_type] = "{A}"
)
)
),
CALCULATE (
SUM ( Bookings[QUANTITY] ),
FILTER ( Bookings, Bookings[QUANTITY] > 0 )
)
)
RETURN
IF ( qty > 0, qty, qty2 )
Below is table for the data:
Type | Con_type | QUANTITY |
inf | Oth | 0 |
Lc | {A}-{N} | 1 |
Lc | {A} | 1 |
inf | Oth | 2 |
inf | {A}-{N} | 0.4 |
Lc | {A}-{N} | 0 |
inf | {A}-{N} | 0 |
inf | Oth | 1 |
Lc | {A} | 10 |
app | {A}-{N} | -1 |
inf | Oth | 2 |
app | Oth | 1 |
inf | Oth | 2 |
Lc | Oth | 1 |
inf | Oth | 2 |
inf | Oth | 0 |
Lc | Oth | 0 |
inf | Oth | 4 |
inf | {ns} | 3 |
inf | Oth | 2 |
inf | Oth | 2 |
inf | Oth | 4 |
inf | Oth | 0 |
inf | Oth | 2 |
inf | Oth | 3 |
app | {A} | 0.5 |
Lc | Oth | 6 |
inf | Oth | 4 |
inf | Oth | 0 |
inf | Oth | 2 |
inf | Oth | 1 |
inf | Oth | 0 |
inf | Oth | 1 |
inf | Oth | 1 |
inf | Oth | 2 |
inf | {ns} | 0 |
inf | {ns} | 0 |
Lc | Oth | 0 |
inf | {ns} | 4 |
app | {A} | 1 |
Lc | {ns} | -6 |
Lc | {ns} | 0 |
app | {A} | 1 |
inf | {A} | 0 |
inf | {A} | 1 |
app | {A} | 1 |
inf | Oth | -0.5 |
please help me out with correcttion in measure.
Thank you,
Solved! Go to Solution.
You can just add that into the Calculate()
SumIF inf %A% =
CALCULATE (
SUM ( 'Type'[QUANTITY] ),
'Type'[QUANTITY] > 0,
FILTER (
'Type',
OR (
'Type'[Type] <> "inf",
AND ( 'Type'[Type] = "inf", SEARCH ( "{A}", 'Type'[Con_type],, 0 ) > 0 )
)
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I am not sure what your second condition means, but here is an expression that I believe does #1 (if not also #2).
SumIF inf %A% =
CALCULATE (
SUM ( 'Type'[QUANTITY] ),
FILTER (
'Type',
OR (
'Type'[Type] <> "inf",
AND ( 'Type'[Type] = "inf", SEARCH ( "{A}", 'Type'[Con_type],, 0 ) > 0 )
)
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you for this one,
It worked for 1st condition but not for second one.
Let me elaborate a little,
if you see the table, it has 0 and negative values in quantity. When a user export this underlying dataset they see 0 and negative values, which I don't want them to show. I want that, if user export they only see values greater than 0. The meausre I have uploaded was giving me that result as I was hiding the original field and giving them this measure to export.
see below picture, I want to avoid those zeros values in drill down and export, therefore taking sum of booked quantity greater than zero.
You can just add that into the Calculate()
SumIF inf %A% =
CALCULATE (
SUM ( 'Type'[QUANTITY] ),
'Type'[QUANTITY] > 0,
FILTER (
'Type',
OR (
'Type'[Type] <> "inf",
AND ( 'Type'[Type] = "inf", SEARCH ( "{A}", 'Type'[Con_type],, 0 ) > 0 )
)
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
It didn't perfectly, but it gave me a Idea which worked for me.
Thank you very much for your help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |