Solved: Show text for average by range

tawfikypc2017

Hi there,
I require assistance in developing a Looker Studio report that analyzes student grade data from an Excel spreadsheet. Specifically, I need to:

Calculate the average grade for a designated subject within each of five classes.
Display the calculated average for each class as a descriptive text label, categorized according to predefined grade ranges. For example:
Average > 80: "Excellent"
60 ≤ Average ≤ 80: "Good"
and so forth, down to 0 ≤ Average < 20: "Poor"
Implement interactive filtering capabilities within the report. Ideally, I should be able to segment the results by additional variables, such as gender (male/female), for granular analysis within specific classes.
Determine if there is a Looker Studio command or methodology that directly converts average score values from scorecards into corresponding text labels based on specified criteria or ranges.
Essentially, I am seeking a solution to dynamically translate numerical average grades into qualitative text descriptions within a Looker Studio dashboard, with the added functionality of interactive data filtering."

Sichali1

Hi,

I hope this finally helps. To begin, create a calculated field named AVG Score. In this field, use the following formula:

CASE

WHEN AVG(Grade) > 80 THEN "Excellent"

WHEN AVG(Grade) >= 60 THEN "Good"

ELSE "Poor"

END

Once you've created this field, drag and drop AVG Score onto the Scorecard. This should display the results you're looking for. You can also apply conditional formatting to the Scorecard so that the background colour changes depending on whether the result is "Excellent", "Good", or "Poor". See the image below for reference. I have also updated the dashboard using the link above for more clarity.

View solution in original post

Sichali1

Hi,

To help you get started, I created a simple dashboard that you can use for inspiration. It uses 200 rows of sample student data from different classes and subjects.

In the dashboard, I calculated the average grade for each subject across five classes using a calculated field:

AVG(Grade)

After that, I added another calculated field that turns those averages into descriptive labels like “Excellent” or “Good.”

CASE

WHEN AVG(Grade) > 80 THEN "Excellent"

WHEN AVG(Grade) >= 60 THEN "Good"

ELSE "Poor"

END

I did this using a CASE formula, which checks where the average falls and assigns a label based on a specific range. For example, if the average is over 80, it shows “Excellent,” between 60 and 80 is “Good,” and so on.

I also included interactive filters, like gender and subject, so you can drill down into the data and analyse specific groups more easily. Let me know if you'd like help building your version or customizing it further.

For reference and to see the expected output, click the link below:

https://lookerstudio.google.com/u/0/reporting/d40ce7b5-ac85-49a8-890a-2bab2886330c/page/vI8GF

tawfikypc2017

Thank you very much dear Sichali for your help.
I don't need many details, just the overall score—excellent, good, or poor.
The text will automatically appear next to the percentage, even when you click on student attributes, such as males and females, etc.
Is it possible to do this?

Sichali1

Hi,
Take a look at the link above. On the column charts, if you hover over them, a little text box will pop up showing some basic info (Attributes)
As for the text next to the percentages, check out the “score” column in the table.

tawfikypc2017

Hi

I need the text appear automatically and
as this pic below:
I mean only the text of the total result will be appe

ar. Or, put another way, we have a question based on a five-point Likert scale, with 1 meaning "strongly agree" and 5 meaning "strongly disagree." I need to display the overall score for the question (the numerical average) in text form so that it's clear to anyone viewing the report: Is the overall score "strongly agree?" etc.? I need it to appear simply, without an image containing the other options.
For example, if the overall average for the question is 1.50, then the overall score is "strongly agree." or
4.5 means "strongly disagree," etc.ex: the total result "Somewhat disagree" of q because the mean =3.96 as the pic below(I need this text automatically appear even i choose another variables as male or female..etc., according to the actual average of the results according to this variable

Sichali1

Hi,

I hope this finally helps. To begin, create a calculated field named AVG Score. In this field, use the following formula:

CASE

WHEN AVG(Grade) > 80 THEN "Excellent"

WHEN AVG(Grade) >= 60 THEN "Good"

ELSE "Poor"

END

Once you've created this field, drag and drop AVG Score onto the Scorecard. This should display the results you're looking for. You can also apply conditional formatting to the Scorecard so that the background colour changes depending on whether the result is "Excellent", "Good", or "Poor". See the image below for reference. I have also updated the dashboard using the link above for more clarity.

tawfikypc2017

Thank you,I think it is now work.