Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
Proud to be a Super User!
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!
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
Proud to be a Super User!
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
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |