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