Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JustinDoh1
Post Prodigy
Post Prodigy

Trying to understand usage/logic of SUMX (outcome is illogical)

I am sharing my Pbix file here.

I am trying to understand about "SUMX" in general (because somebody suggested to use it), but I am not sure how it works. I got the impression that SUMX counts all occurrence/evaluation in the row context.

 

I decided to use "SUMX" because I have cases where I have to make the outcome as "", and if I do not use SUMX, Total comes out to be "" in Maxtrix, and that is not it should look.

 

I am working on measures called "Step1_0821" & "Step1 0821 SUMX".

 

According to the logic, it should generate 1 in both"Step1_0821" & "Step1 0821 SUMX", but on "Step1 0821 SUMX", the outcome is 2, and that is why I am trying to get a feedback from the community.

JustinDoh1_1-1629645343605.png

 

JustinDoh1_1-1629646670835.png

 

JustinDoh1_4-1629645819690.png

If I understand the flow of logic correctly in this case, the logic did not meet the requirement for vNotEligible, so it goes to the final choice option, Step1, for the outcome of 1.

 

But, with the logic of Step1, it should generate only 1 row for Step1 0821 SUMX or I am writing the DAX formula correctly.

Where am I missing the logic?

 

Please help me. Thank you so much!

 

 

1 ACCEPTED SOLUTION

@JustinDoh1 Right, in general you either use CALCULATE or SUMX. When you mix the two you could definitely get some odd results.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@JustinDoh1 Sorry, I don't see SUMX in your formulas? Reasons to use SUMX. SUMX accepts a table expression as the first parameter so you can filter it however you please and then SUMX takes the second parameter, evaluates the expression for each row of the table and then sums the results together. You can see the difference between SUMX and CALCULATE. Take a simple table like below:

ItemQuantityValue

One 5 5
Two 10 10
Three 20 20

 

And the following measures:

Sumx1 = SUMX(FILTER('Items',[Item]<>"Two"),[Quantity]*[Value])

Caculate1 = CALCULATE(SUM('Items'[Quantity])*SUM('Items'[Value]),'Items'[Item]<>"Two")

SUMX returns the correct answer 425 while CALCULATE returns the wrong answer 625. SUMX got the right answer because it evaluated at the row level, 5*5 + 20*20. CALCULATE got it wrong because it calculated (20 + 5) * (20 + 5). 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thank you for your feedback. I was using SUMX calling measure "Step1_0821" like this:

JustinDoh1_0-1629649730643.png

So, you suggest to use SUMX on this area directly?

JustinDoh1_1-1629649846407.png

 

@JustinDoh1 Right, in general you either use CALCULATE or SUMX. When you mix the two you could definitely get some odd results.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thank you so much. I am getting close to make this work.

When I tried with two criteria within filter, it would not give me error.

But when I tried with more than two, it would not let me, but gives me an error.

Do I go about doing this?

 

JustinDoh1_0-1629692472683.png

 

@JustinDoh1 Your NOT(ISBLANK is missing a closing paren. Should be 

 

NOT(ISBLANK(Table1[Consent]))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thank you so much. I was searching for an example online for "SUMX multiple filters", and only found examples up to 2 filters, so I lost a confidence. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.