Forum Discussion
Sum with multiple criteria
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.
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.
8 Replies
kg4u you need to update a measure like this, I would not go with the path suggested by Anonymous
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.
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.
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.
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" ) )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
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.
I tired both the options as suggested and neither is returning the results as expected.
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.
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")
