Average by Column then Percent Difference

[stark1687]

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? 

[v-yuta-msft]

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

[stark1687]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.  Hope this helps.

[Gattinomio]

Hi.

This is exactly what I am looking for but I am unable to download the file.
Is it still available?

Thanks a lot in advance.

[Ashish_Mathur]

Hi,

I do not have the file.  Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Gattinomio]

Hi,
thanks a lot for your answer.

I am trying to calculate the average of the values within a specific category and to then calculate the percentage change from the average for each of these values .

Fruits Category	Fruits	Volume	Current Average Calculation	Desired Average Calculation	Percentage Change
Berries	Blueberry	31	31	1,212	-97.44%
	Boysenberry	329	329	1,212	-72.86%
	Cranberry	65	65	1,212	-94.64%
	Currants	7,297	7,297	1,212	501.92%
	Gooseberry	66	66	1,212	-94.56%
	Loganberry	225	225	1,212	-81.44%
	Raspberry	473	473	1,212	-60.98%
	Total	8,486	1,212	1,212

The "Current Average Calculation" in blue is what I am currently getting with the following formula:

# Total Average by Fruits =

AVERAGEX(

        KEEPFILTERS(VALUES(Products[Fruits])),

        CALCULATE([Volume]))

The total is correct but the problem is that I would like to have this total displayed on each row (Desired Average Calculation) instead to then be able to calculate the percentage change. Exactly what you showed in your screenshot above.

As a side note, the fruits category is selected in the filter panel and it applies for the whole page but there are also some slicers on the dashboard. I would like these calculations to adjust accordingly to the filters selected in the slicers.

Many thanks in advance for your input.

Any suggestion would be really appreciated.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Gattinomio]

Thank you very much.

I found the solution by myself in the end.

[stark1687]

That worked thanks 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Paste the column titles with your data.  How does one know what is the info in that column without a title?

[stark1687]

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