Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 the average.
001967 | $ 3,694,483.24 | CI | 41371 |
002604 | $ 2,396,125.21 | CI | 37608 |
002605 | $ 1,219,674.86 | CI | 41371 |
003442 | $ 776,005.65 | CI | 41371 |
003457 | $ 883,514.22 | CI | 3394 |
003458 | $ 1,273,172.80 | CI | 3394 |
003462 | $ 1,606,554.29 | CI | 38528 |
003475 | $ 1,299,846.38 | CI | 3394 |
004153 | $ 512,749.13 | CI | 41371 |
Looking at this data the average across all vendors (by event bc there are lots of different events in the table) is 1,550,728.22 but for vendor 37608 it is 2,396,125.21 so it is 55% greater than the average. Indicating that this vendor is very expensive or the project went bad.
In this way I want to show which vendors are costing more than the average cost to perform a work scope.
This can be done quickly in excel and pivot tables but we want the dashboard/interactive reports of Power BI.
I keep getting stuck on how to write the Average Measure to produced the desired resuts given that my table has lots of vendors for lots of differnet events.
Could someone point me towards a good tutorial?
Solved! Go to Solution.
Hi,
You may download my solution file from here. Hope this helps.
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 |
Hi,
You may download my solution file from here. Hope this helps.
That worked thanks
You are welcome.
Hi,
Paste the column titles with your data. How does one know what is the info in that column without a title?
Sorry didnt realize I missed the headers
Project | Cost | Event | Vendor Code |
1967 | $ 3,694,483.24 | CI | 41371 |
2604 | $ 2,396,125.21 | CI | 37608 |
2605 | $ 1,219,674.86 | CI | 41371 |
3442 | $ 776,005.65 | CI | 41371 |
3457 | $ 883,514.22 | CI | 3394 |
3458 | $ 1,273,172.80 | CI | 3394 |
3462 | $ 1,606,554.29 | CI | 38528 |
3475 | $ 1,299,846.38 | CI | 3394 |
4153 | $ 512,749.13 | CI | 41371 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |