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 brand new to Power BI and I've got a complicated question already.
I have a bunch of survey results that can be grouped by a number of different buckets. The survey questions are also grouped into categories.
I organized my visualization to show an average score by category. My tooltip shows the Category and the Average score (with no decimals).
I want to add a description to the tooltip, based on the average answer. For example, average score = 2 would return a description of "Level 2 means some projects are using best practice but not many."
I have data that links the score (1-5) and the description. In Excel terms I would do a vlookup: (average score,table of scores & descriptions,description column number, true). I can't figure out how to reference the average score and use that to find the related description. When I try bringing descriptions into the Tool Tips, I just get "Count" instead of the text.
Is this possible??
Thank you!
Assuming average score is a measure, write a new measure that returns some text description based on values for the average.
Tooltip Description = SWITCH(
[Average Score],
1, "Level 1 means something something something.",
2, "Level 2 means some projects are using best practice but not many.",
3, "Level 3 means something else something something.",
4, "Level 4 means you probably get the idea by now.",
5, "Level 5 means I'm still typing this example.",
"This is a default for when the average score is blank or 6 or 97 or whatever.")
Then put that measure into the tooltip box.
Proud to be a Super User!
Average score is the Quick Calc of the Value in the visualization. Does that change the approach?
This is why I always write out measures explicitly instead of relying on quick calcs.
Tooltip Description = SWITCH(
AVERAGE(TableName[ColumnName]),
1, "Level 1 means something something something.",
2, "Level 2 means some projects are using best practice but not many.",
3, "Level 3 means something else something something.",
4, "Level 4 means you probably get the idea by now.",
5, "Level 5 means I'm still typing this example.",
"This is a default for when the average score is blank or 6 or 97 or whatever.")
...where TableName[ColumnName] is whatever thing you put in the value box to get that average.
Proud to be a Super User!
Thank you, i will give it a try!
In this scenario, if you have a table which includes data that links the score (1-5) and the description, you can use LOOKUPVALUE() to get the description and show it in the Tooltips. Please refer to following steps.
I assume you have two tables as below
AverageScore_PerCategory = ROUND ( CALCULATE ( AVERAGE ( ScoresTable[Scores] ), ALLEXCEPT ( ScoresTable, ScoresTable[Category] ) ), 0 )
Score_Description = LOOKUPVALUE ( 'Description'[Description], 'Description'[Score], [AverageScore_PerCategory] )
The solution using SWITCH() which provided by @KHorseman should also be OK.
Regards,
I tried your approach and the measure for average score is working, although I still needed to use the "Average" quick calc to show the correct values. However the description lookup doesn't bring back full results and still shows up as a "Count" in the tool tips. Do I need to turn off quick calcs?
@v-sihou-msft
I had a filter turned on which is why the description lookup was missing some results. When I realized this, I saw that when I use the calculated measure in the visualization, the report filters don't work on it. So I want to keep my original data and use the Quick Calc to show the average by category. When I do this, I still see a Count of description in Tool Tips.
I still have this issue when I try the "SWITCH" formula too. Both are doing the right thing and looking up the description that corresponds to the score. But the tool tip still displays "Count" instead of the description itself.
@inthemeadow are you creating a MEASURE? From what you are describing looks like you are creating a COLUMN!
YES that was the problem
Thank you for correcting me. It works perfectly now and I'm very happy with my dashboard!!!!
No, no, no I spoke too soon. It isn't working perfectly.
Now that I'm using MEASURE I do see the text in the Tool Tip so that is working. Thank you!!
The problem is with the
RoundAverageScore_PerCapability = round (calculate (average('Fact1 (3)'[Answer]),allexcept('Fact1 (3)','Fact1 (3)'[Capability])),0)
This is bringing back the overall average of all the answers combined, instead of separating it out by Capability. Therefore the ToolTip is showing the same definition for each Capability, instead of a different one per Capability, based on the average answer for that Capability.
@inthemeadow Assuming your Fact1 (3) table is setup as @v-sihou-msft did in the test file he had created
There's nothing wrong with the MEASURE => [RoundAverageScore_PerCapability]
Have you tried this MEASURE (it also works!)
Tooltip Description = SWITCH ( [RoundAverageScore_PerCapability], 1, "Tooltip 1", 2, "Tooltip 2", 3, "Tooltip 3", 4, "Tooltip 4", 5, "Tooltip 5", "Off the Chart!" )
Same problem, it is returning Tooltip 2 for all the values.
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |