Forum Discussion
Sum of elements
Here's my issue: I have a formula calculating a refund amount by product for over/under consumption. Idea is refund/charge for more than 10% over/under consumption:
Refund/Charge = (switch(TRUE(),[Consumption Qty]<[Allocation -10%],[Allocation-Consumption]-[Allocation]*.10,[Consumption Qty]>[Allocation +10%],[Allocation-Consumption]+[Allocation]*.10,0))*[Net Price]
The formula works just fine by chemical:
Output
Chemical 1 (3940)
Chemical 2 250
Chemical 3 (45) etc
The problem is, I need a total too. Total given is $7, but total of refund/charge for each chemical (adding each line item) is 10,319.
7 Replies
Assuming you are using this in something like a table/matrix and that this is a measure, you often have to change how this is calculated in the context of ALL. Typically do this with an IF statement and HASONEFILTER or HASONEVALUE I believe, it is a pretty common issue.
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
the problem is hasonevalue applies to columns, not measures.
Using a column isn't realistic in this case. I'd like something that does the same thing for a measure.
Use HASONEFILTER.
