Sum with multiple criteria

[kg4u]

Hi,

I have reviewed and searched on this forum for ideas and suggestions.  While I did find a couple of suggestions which I thought would work, neither option I found is giving me the results as expected.

I have been trying to get a sum work with multiple criteria.  I have tried both the following formulas. Both of these formulas are seem to be correct in desktop, meaning I am not getting an error on the actual syntax of these formulas. 

This formula does give a result, however it is not the correct result.

Quality Sales = CALCULATE(

[Total Sales],

'Fact Agency Data'[Status] in {"Issue Paid", "Issue Paid|ARF", "Issue Paid|PDR", "Issued Paid|NY PIF", "Issued Paid|PDR|NY PIF" }

)

This formula doesn't return any results.

Quality Sales2 = CALCULATE(

SUM('Fact Agency Data'[Original Sales]),

FILTER ('Fact Agency Data','Fact Agency Data'[Status] = "Issue Paid"),

FILTER ('Fact Agency Data','Fact Agency Data'[Status] = "Issue Paid|ARF"),

FILTER ('Fact Agency Data','Fact Agency Data'[Status] = "Issue Paid|PDR"),

FILTER ('Fact Agency Data','Fact Agency Data'[Status] = "Issue Paid|NY PIF")

)

Total Sales = Sum ('Fact Agency Data'[Original Sales])

I am simply trying to get the value [Total Sales] when the [Status] = A or B or C or D, can someone give a suggestion on how to create this simple measure?

Many thanks to you power users 🙂

[parry2k · Accepted solution]

@kg4u you are using wrong value in your measure, missing "d" from some of the value, here is the correct measure. Pay attention to your data.

Quality Sales = 
CALCULATE (
    [Total Sales],
    KEEPFILTERS (
        'Quality Data'[Status] in { "Issued Paid", "Issued Paid|ARF", "Issued Paid|PDR", "Issued Paid|NY PIF", "Issued Paid|PDR|NY PIF" }
    )
)

and here is the output

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Ashish_Mathur]

Hi,

Does this measure work

Quality Sales2 = CALCULATE(SUM('Fact Agency Data'[Original Sales]),'Fact Agency Data'[Status] = "Issue Paid"||'Fact Agency Data'[Status] = "Issue Paid|ARF"||'Fact Agency Data'[Status] = "Issue Paid|PDR"||'Fact Agency Data'[Status] = "Issue Paid|NY PIF")

If it does not, then share the download link of your PBI file.

[kg4u]

I tired both the options as suggested and neither is returning the results as expected.

Quality Sample 

I think I have a copy of the file with the data and measures linked above for your review.  I certainly do appreciate your assistance.  

[Ashish_Mathur]

Wrong spelling - use "issued" instead of "issue".  Try this measure

Quality Sales2 = CALCULATE([Total Sales],'Quality Data'[Status] = "Issued Paid"||'Quality Data'[Status] = "Issued Paid|ARF"||'Quality Data'[Status] = "Issued Paid|PDR"||'Quality Data'[Status]= "Issued Paid|NY PIF")

[parry2k]

@kg4u you need to update a measure like this, I would not go with the path suggested by @Daryl-Lynch-Bzy 

Quality Sales = CALCULATE(

[Total Sales],
KEEPFILTERS(
'Fact Agency Data'[Status] in {"Issue Paid", "Issue Paid|ARF", "Issue Paid|PDR", "Issued Paid|NY PIF", "Issued Paid|PDR|NY PIF" }
)
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Daryl-Lynch-Bzy]

Hi @kg4u 

Try the following as the syntax "||" works as the OR operator.

Quality Sales2 =
CALCULATE (
    SUM ( 'Fact Agency Data'[Original Sales] ),
    FILTER (
        'Fact Agency Data',
        'Fact Agency Data'[Status] = "Issue Paid"
            || 'Fact Agency Data'[Status] = "Issue Paid|ARF"
            || 'Fact Agency Data'[Status] = "Issue Paid|PDR"
            || 'Fact Agency Data'[Status] = "Issue Paid|NY PIF"
    )
)

[kg4u]

I tried with the pipes, and I'm not getting a result in the data table.  The Quality Sales Number should be $15,547,136.23

Attached is a snippet of the dataset I'm working with. The Quality number for this snippet should be $9,183.35.

Original Sales	Status
$748.00	Issued Paid
$62.70	Issued Paid
$1,485.00	Issued Paid
$1,731.95	Issued Paid
$791.45	Issued Paid
$1,826.00	Issued Paid
$2,538.25	Issued Paid

[parry2k]

@kg4u your measure below should work, how are visualizaing the data which is making you think it is not working as expected:

Quality Sales = CALCULATE(

[Total Sales],

'Fact Agency Data'[Status] in {"Issue Paid", "Issue Paid|ARF", "Issue Paid|PDR", "Issued Paid|NY PIF", "Issued Paid|PDR|NY PIF" }

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.