Forum Discussion
Solved
Subtract two total num values from same column using a filter from other column in the same table
Hi everyone, I am really new using PowerBi and I am trying to find the way to create a measure using DAX code to receive the result of subtracting two total num values from same column using a fi...
- Hi jasanlea ,Please note that below measure will remove all filters on table and calculate sum for reference value =1 and 2 and subtract them. If it is something else you want then please explain in detail.testtable4-----------ReferenceValue2024
1 5 2 3 Measure------------Measure Sub =VAR _refVal1 = CALCULATE(SUM(TestTable4[2024]), REMOVEFILTERS(TestTable4[ReferenceValue]), TestTable4[ReferenceValue] = 1)VAR _refVal2 = CALCULATE(SUM(TestTable4[2024]), REMOVEFILTERS(TestTable4[ReferenceValue]), TestTable4[ReferenceValue] = 2)return IF(HASONEFILTER(TestTable4[ReferenceValue]), SELECTEDVALUE(TestTable4[2024]), _refVal1 - _refVal2)
Hi jasanlea ,
Please note that below measure will remove all filters on table and calculate sum for reference value =1 and 2 and subtract them. If it is something else you want then please explain in detail.
testtable4
-----------
ReferenceValue2024
| 1 | 5 |
| 2 | 3 |
Measure
------------
Measure Sub =
VAR _refVal1 = CALCULATE(SUM(TestTable4[2024]), REMOVEFILTERS(TestTable4[ReferenceValue]), TestTable4[ReferenceValue] = 1)
VAR _refVal2 = CALCULATE(SUM(TestTable4[2024]), REMOVEFILTERS(TestTable4[ReferenceValue]), TestTable4[ReferenceValue] = 2)
return IF(HASONEFILTER(TestTable4[ReferenceValue]), SELECTEDVALUE(TestTable4[2024]), _refVal1 - _refVal2)
It worked! Thank you so much. 🙂
Is there is a way so I can use a condittional formatting so the result shows an green icon for the positives and a red one for the negatives?
Something like this:
Thanks again!
jasanlea ,
You're welcome.
Please test out all scenarios.
Please create these two measures
Measure Sub Arrow = IF( [Measure Sub] >= 0, UNICHAR(129129), UNICODE(129131))Measure Sub Color = IF( [Measure Sub] >= 0, "Green", "Red")Also change to original measure in case there are multiple values for Reference 1 or 2.------------------------------------------------------------------------------------------------------------Measure Sub =VAR _refVal1 = CALCULATE(SUM(TestTable4[2024]), REMOVEFILTERS(TestTable4[ReferenceValue]), TestTable4[ReferenceValue] = 1)VAR _refVal2 = CALCULATE(SUM(TestTable4[2024]), REMOVEFILTERS(TestTable4[ReferenceValue]), TestTable4[ReferenceValue] = 2)VAR _selectedVal = SELECTEDVALUE(TestTable4[ReferenceValue])return IF(HASONEFILTER(TestTable4[ReferenceValue]), IF(_selectedVal = 1, _refVal1, _refVal2), _refVal1 - _refVal2)------------------------------------------------------------------------------------------------------------- In table formatting set cell elements > font color for measure "Measure Sub", use formula "Measure Sub Color"- For totals use a card and overlay table, for Card use "Measure Sub Arrow", go to Values and set color to formula "Measure Sub Color"