Forum Discussion
Solved
Average by Column then Percent Difference
New PowerBI user here. I have a set of data of project spend and I am trying to get the averages based upon two columns "Event" and "Vendor". Then I want to compare actuals to %difference of...
Hi stark1687,
Create a calculate column and try DAX like this:
Percentage =
CALCULATE (
DIVIDE ( table[cost] - AVERAGE ( table[cost] ), table[cost] ),
FILTER ( table, table[event] = EARLIER ( table[event] ) )
)
Regards,
Jimmy Tao
Didnt quite work.
My table has lots of different values for Event Type, Project and Vendor more like this.
So for event CI the avg cost for vendor 3394 is 1,152,177.8, the avg for all vendors is 1,1518,13.97. So this vendor is pretty well aligned with the market. Whereas the avg for vendor 37608 is 2,396,125.21 which is way above. But I want to create a measure/column to do this analysis in the table that has lost of different vendors/events for each project.
| ProjectID | Sum of ActualCost | EventType | Vendor.1 |
| MM003457 | 883,514.22 | CI | 3394 |
| MM003458 | 1,273,172.80 | CI | 3394 |
| MM003475 | 1,299,846.38 | CI | 3394 |
| MM002604 | 2,396,125.21 | CI | 37608 |
| MM003462 | 1,606,554.29 | CI | 38528 |
| MM001967 | 3,694,483.24 | CI | 41371 |
| MM002605 | 1,219,674.86 | CI | 41371 |
| MM003442 | 776,005.65 | CI | 41371 |
| MM004153 | 512,749.13 | CI | 41371 |
| MM002632 | 2,035,097.14 | HGP | 3394 |
| MM003384 | 2,293,902.88 | HGP | 3394 |
| MM003390 | 6,977,007.28 | HGP | 3394 |
| MM003412 | 2,410,819.81 | HGP | 3394 |
| MM003429 | 6,320,683.18 | HGP | 3394 |
| MM003441 | 5,393,296.40 | HGP | 3394 |
| MM006737 | 2,708,156.58 | HGP | 3394 |
| MM003433 | 5,649,589.79 | HGP | 38528 |
| MM003492 | 1,426,222.69 | HGP | 38528 |
| MM003482 | 745,867.94 | HGP | 41371 |