Forum Discussion
Subtraction values from one column based on different criteias
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!
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 - __cost3Then 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
3 Replies
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 - __cost3Then 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
