Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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])
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
Please see atatched workspace
Create a new column and then add it to the table.
Scenario = IF(Sheet1[Sales]>200, Sheet1[Sales],0)
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 ?
@Anonymous
Create the following measure:
Filtered Values = SUMX(VALUES(Sheet1[DistName]),CALCULATE(SUM(Sheet1[Scenario])))
Scenario is the new column which is created before
Dear @themistoklis,
You are man of the day. Huge thanks for your efforts to understand my issue and collaboration.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |