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.
I have the 3 first columns, and I want to get the blue one. The values are obtained by comparing grades to grade averages of each area. How to do it?
Solved! Go to Solution.
Hi @Zyg_D ,
Try this one:
Hi @Zyg_D
Create a new calculated column in your table:
above_below =
VAR area_avg_ =
CALCULATE ( AVERAGE ( Table1[grade] ), ALLEXCEPT ( Table1, Table1[area] ) )
RETURN
SWITCH (
TRUE (),
Table1[grade] > area_avg_, "above average",
Table1[grade] = area_avg_, "average",
Table1[grade] < area_avg_, "below average"
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
@AlB wrote:Create a new calculated column in your table:
above_below =
VAR area_avg_ =
CALCULATE ( AVERAGE ( Table1[grade] ), ALLEXCEPT ( Table1, Table1[area] ) )
RETURN
SWITCH (
TRUE (),
Table1[grade] > area_avg_, "above average",
Table1[grade] = area_avg_, "average",
Table1[grade] < area_avg_, "below average"
)
Although I like that you properly formatted your answer, it evaluated as error to me.
Hi @Zyg_D ,
Try this one:
@Greg_Deckler wrote:
above/below =
VAR __grade = MAX('Table'[grade])
VAR __area = MAX('Table'[area])
VAR __average = AVERAGEX(FILTER(ALL('Table'),[area]=__area),[grade])
IF(__grade >= __average,"above average","below average")
Sorry, even after adding RETURN before IF it yields wrong results - always above average.
Because it is a measure and you are probably trying to use it in a column.
Column form:
above/below =
VAR __grade = [grade]
VAR __area = [area]
VAR __average = AVERAGEX(FILTER('Table',[area]=__area),[grade])
RETURN
IF(__grade >= __average,"above average","below average")
@Greg_Deckler wrote:Because it is a measure and you are probably trying to use it in a column.
Column form:
above/below =
VAR __grade = [grade]
VAR __area = [area]
VAR __average = AVERAGEX(FILTER('Table',[area]=__area),[grade])
RETURN
IF(__grade >= __average,"above average","below average")
Is performance-wise measure better than calculated column?
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |