Forum Discussion
Mesure - Sum values with condition
Hello experts!
I am struggeling with the following scenario:
I have a daily target column which I need to sum and to show the results for an employee and customer level.
When I put this column into a matrix it works but sometimes I get negative results and I need to ignore them so those values won't affect the the total results.
So I created the following query:
In the first image you can see that the query works and replaces the negative value by 0, but in the total result it still relates to all negative values.
I expect the following to happen - ignore the negative values, and to get different result.
Anyone knows how to solve it?
Thanks in advance!
3 Replies
Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Hi Greg!
I looked at the posts you sent in your response and I created the following two measures (but still have the same problem):
Single measure (Sum of Daily Target) =IF(SUM(EMP_CUS_Target[Daily Target])<0,0,SUM(EMP_CUS_Target[Daily Target]))Final Sum of Daily Target =VAR __table = SUMMARIZE(EMP_CUS_Target,EMP_CUS_Target[Employee_Number],EMP_CUS_Target[Customer_ID],"__value",[Single measure (Sum of Daily Target)])RETURNIF(HASONEVALUE(EMP_CUS_Target[Employee_Number]),[Single measure (Sum of Daily Target)],SUMX(__table,[__value]))The thing is that in my report I show employees and customers from Dim_employees and Dim_customers tables that connected to EMP_CUS_Target table (by employee_ID\customer_ID keys).And I think this is the reason why it doesnt work for me as the examples you showcased in the posts were related to columns from the same table.Do you know what should I do in this case?Could you share a sample pbix after removing sensitive data so that we can know your scenario better
