Power Pivot Measure to calculate average rate of by profession - All external filters should apply

[Waka]

Hi Team,

Same story, I am new to power pivots and learning DAX,

a) I have the following dataset and I want to compare average price of an individual to compare against the average price of the profession he is in. Currently I have calculated the last column "Avg. by Profession using regular AVERAGEIFS i.e. =AVERAGEIFS([Price],[Profession],[@Profession] I'm not sure how to achieve the same results using calculate columns in DAX

 b) I want to pull the same average (i.e. professions category average) in a power pivot but without pulling in profession in the rows, columns or filters. The final output should look like this.

but I'm struggling with calculating Average by Table1[Profession] Column in the above picture, I have tried using DAX =CALCULATE(AVERAGE(Table1[Price]),ALLEXCEPT(Table1,Table1[Profession])) but it is not giving the results in the picture, The wrong view is as below:

Can you guys please help me? Note: I want external filters to be applied in the measure, I have a large dataset over several years, so I want to be able to put the years in the filter and in that case the category average should only be based on values within that selected year. 

[ryan_mayu · Accepted solution]

@Waka 

you can try a column

avg by profession = CALCULATE(AVERAGE('Table'[Price]),ALLEXCEPT('Table','Table'[Profession]))

and create measures

avg price = AVERAGE('Table'[Price])

avg of profession2 = AVERAGe('Table'[avg by profession])

rate vs average = [avg of profession2]-[avg price]

pls see the attachment below

[Waka · Accepted solution]

Thanks @ryan_mayu , the whole solution worked, only small glitch was that the averages were not changing when I put the year in power pivot filters. I modified "avg by profession" calculation with following formula to include year as well and it worked like a charm.

avg by profession = CALCULATE(AVERAGE('Table'[Price]),ALLEXCEPT('Table','Table'[Profession],'Table'[year]))

 I hope it is the correct way to making the values change by year selection in the filter / slicer. 
Thanks!

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@Waka 

you can try a column

avg by profession = CALCULATE(AVERAGE('Table'[Price]),ALLEXCEPT('Table','Table'[Profession]))

and create measures

avg price = AVERAGE('Table'[Price])

avg of profession2 = AVERAGe('Table'[avg by profession])

rate vs average = [avg of profession2]-[avg price]

pls see the attachment below