Subtraction values from one column based on different criteias

[SaraM]

I have a table that I want to subtract values of " Estimated cost" column based on clasee of Estiame and Description.

Here is a smple of my table. can you please help me with the query. 

I want column "cost variance" to subtract line items with the same discriptin & Cost Category between class of estimate 4 and 3. 

Thank you!

[BA_Pete · Accepted solution]

Hi @SaraM ,

In Power Query you can select your [CLASS OF ESTIMATE] column, then go to the Transform tab > Pivot Column.

Set [Estimated Cost] as the Values Column, and set the aggregator to SUM under Advanced Options.

This will give you the different estimate classes as columns that you can add/subtract across.

The more common way to achieve this, however, would be to load your table to the data model as it is, then write a measure against it, something like this:

_costVariance =
VAR __cost3 =
CALCULATE(
  SUM(yourTable[Estimated Cost]),
  yourTable[CLASSOFESTIMATE] = 3
)
VAR __cost4 =
CALCULATE(
  SUM(yourTable[Estimated Cost]),
  yourTable[CLASSOFESTIMATE] = 4
)
RETURN
__cost4 - __cost3

Then you would add this measure to your visuals along with whichever categories/descriptions etc. you want and Power BI will agregate the variance for each dimension roup for you.

Pete

[SaraM]

@BA_Pete  Thank you so much. It worked perfectly with both, but I went with the measure. 

Thank you!

[BA_Pete]

@SaraM ,

No problem. Happy to get it sorted for you so quickly.

Don't forget to mark the post that solved your issue as the solution. This helps others with the same question find the answer quicker when searching.

Pete