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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sumif function only value restriction

Hi Everyone,

 

I have been struggling an issue for hours about dax sumif function. Having I rigid searched I have found CALCULATE(SUM(table)FILTER(table,column)] replies. But these functions does not meet my need. As far as I understand this formula is valid for sum range() and ex.("apple","yellow") etc. But I need just one restriction which is sum (range) if>200, Namely, I want to make number filter not color city etc. You can see what I want in below picture. Scenario column must be sum of >200 of brüt satış, not same value. Summary, if brüt satış row 856, scenario row must be 856 but if brüt satış 34, scenario is 0 or blank. Totally, I want to see sum of sales for 1077 Gıda just higher than 200 per row.

 

power bı picture.PNG

 

Thank you.

1 ACCEPTED SOLUTION

@Anonymous

 

Create the following measure:

Filtered Values = SUMX(VALUES(Sheet1[DistName]),CALCULATE(SUM(Sheet1[Scenario])))

Scenario is the new column which is created before

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Can you please help me about this issue ?

 

Thanks in advance

@Anonymous

 

Although it is a bit difficult to understand the logic, can you try something like the following.

 

Create a new column:

New_Column = IF([brüt satış] > 200, [brüt satış], 0)

Then on the measure called Scenario:

Scenario = SUM([New_Column])

 

 

 

 

Anonymous
Not applicable

Dear @themistoklis,

 

Thanks for your reply. But it did not work. I have tried to use calculate(table,filter(column>200)) formula for sumif logic but it did not work too.

@Anonymous

 

Can you show on excel how you want the formula to work?

Input and Output data

Anonymous
Not applicable

Sure @themistoklis. You can see the picture below.

 

sumif ex..PNG

 

@Anonymous

 

Please see atatched workspace

 

Create a new column and then add it to the table.

 

Scenario = IF(Sheet1[Sales]>200, Sheet1[Sales],0)
Anonymous
Not applicable

Dear @themistoklis ,

 

Thanks for huge help. It works but another issue has been occured which is wrong total. nonfiltered sum(brüt satış) and filtered sum(scenario) is same but filtered some (>200 ) should have been lower than unfiltered ones. You can see the picture below. Can you fix this ?

 

Total Issue.PNG

@Anonymous

 

Create the following measure:

Filtered Values = SUMX(VALUES(Sheet1[DistName]),CALCULATE(SUM(Sheet1[Scenario])))

Scenario is the new column which is created before

Anonymous
Not applicable

Dear @themistoklis,

 

You are man of the day. Huge thanks for your efforts to understand my issue and collaboration.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.