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.
Hello, I'm looking to produce a calculated measure that is able to return an average for the category that the row item is in. See example below:
Color | Sale ID | Sale Price | Average by Color |
Green | 1 | 10 | 15 |
Green | 2 | 10 | 15 |
Green | 3 | 20 | 15 |
Green | 4 | 20 | 15 |
Yellow | 5 | 2 | 5 |
Yellow | 6 | 8 | 5 |
Yellow | 7 | 5 | 5 |
Blue | 8 | 30 | 40 |
Blue | 9 | 50 | 40 |
I'm essentially looking for the DAX version of the "AverageIF" function in excel. While I am able to create a summary table displaying the colors and average by colors, that is not what I am trying to achieve. The table needs to be displayed as above, with the individual sales included. Is there a way for CALCULATE to generate a filter context based on the row context of each item?
Any help is appreciated!
Solved! Go to Solution.
CALCULATE(AVERAGE(Table[Sales]),ALLEXCEPT(Table,Table[Color]))
CALCULATE(AVERAGE(Table[Sales]),ALLEXCEPT(Table,Table[Color]))
Thank you, this does what I had requested. I have hit another issue, if you are able to answer this question as well:
I will need this data displayed on a line graph where the data from column 'color' is not displayed. In effect, the table would look like this:
ID | Price | Average by Color |
1 | 10 | 15 |
2 | 10 | 15 |
3 | 20 | 15 |
4 | 20 | 15 |
5 | 2 | 5 |
6 | 8 | 5 |
7 | 5 | 5 |
8 | 30 | 40 |
9 | 50 | 40 |
With the above solution, the calculated measure doesn't work if the 'color' column isn't in the graphic, even though the relationship exists in the data table. Do you know of a way around this?
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |