Need Help in calculating sum with condition

[ssharm43]

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,

[mahoneypat]

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

[ssharm43]

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.

[mahoneypat · Accepted 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

[ssharm43]

It didn't perfectly, but it gave me a Idea which worked for me.

Thank you very much for your help