Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Waka
New Member

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

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

  

Waka_0-1686366041912.png

 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.

Waka_1-1686366506582.png

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:

Waka_2-1686366896526.png

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. 


2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@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]

1.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Waka
New Member

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!

View solution in original post

3 REPLIES 3
Waka
New Member

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!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@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]

1.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.