Sumif function only value restriction

[Anonymous]

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.

Thank you.

[Anonymous]

Can you please help me about this issue ?

Thanks in advance

[themistoklis]

@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]

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.

[themistoklis]

@Anonymous

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

Input and Output data

[Anonymous]

Sure @themistoklis. You can see the picture below.

[themistoklis]

@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]

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 ?

[themistoklis · 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

[Anonymous]

Dear @themistoklis,

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