Forum Discussion
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 filter from other column in the same table.
The example would be as follows:
- Filter column name: "Reference" with values '1' or '2'
- Total values in column name: "2024"
So imagine, the total value of 2024 using filter '1' is €5m and using '2' is €3m, so the result should be €2m.
Thank so much in advance!
Best regards,
Javier
- 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)
4 Replies
- 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"
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
