Subtract two total num values from same column using a filter from other column in the same table

[jasanlea]

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

[talespin · Accepted solution]

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)

[jasanlea]

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!

[talespin]

@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"

[lbendlin]

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-...

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/1447...