Show gauge chart data as percentage of average

[RobSym87]

I have a report that is pulling data from a spreadsheet column, the minimum value in the total column is 0 and the maximum is 60. I want to display the average score on a gauge chart as a percentage however I am only able to get the percentage to display as the grand total (in the thousands). 

For example:

The value for "subcontractor total rating" is set to "average of total" = 40 (out of 60). I want the gauge chart to display 66.6%

I'm very new to PowerBi and self taught and sadly don't know how to achieve this. 

[v-binbinyu-msft · Accepted solution]

Hi @RobSym87 ,

Please try below steps:

1. Calculate the Average Score as a Measure:  You need to create a measure to calculate the average of the total rating. You can do this using the DAX formula provided:

AverageScore = AVERAGE(YourTable[TotalRating])

2. Convert the Average Score to a Percentage: Next, create another measure to convert the average score to a percentage of the maximum score (which is 60 in your case):

AveragePercentage = DIVIDE([AverageScore], 60, 0) * 100

This measure divides the average score by the maximum score and then multiplies by 100 to get a percentage.

3. Create a Gauge Chart: Follow these steps to set up your gauge chart:
- In Power BI Desktop, click on the "Visualizations" pane.
- Select the "Gauge" icon to create a new gauge chart on your report canvas.
- Drag the `AveragePercentage` measure created in step 2 to the 'Value' field of the gauge chart.

4. Set Minimum and Maximum Values for the Gauge Chart:
- Set the minimum value to 0.
- Set the maximum value to 100 (as we are dealing with percentages).

5. Format the Gauge Chart:
- Go to the 'Format' section in the Visualization pane.
- Adjust the data label to display as a percentage. You can do this by selecting the data label and then choosing the 'percentage' format option.

By following these steps, your gauge chart should now display the average score as a percentage of the total possible score, which will be 66.6% if the average score is 40.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RobSym87]

This looks very promising I'll give it a shot. I assume as we are working with DAX formula there is no way of achieving this in the Power BI Online version? 

Thank you for the very comprehensive response 🙂

[v-binbinyu-msft]

Hi @RobSym87 ,

Power BI allows users to modify existing data models in the Power BI service using actions such as editing relationships, creating DAX measures and managing RLS.

Editing data models in the Power BI service is automatically enabled for semantic models stored in My Workspace. To open the data model for semantic models stored in collaborative workspaces, you must turn on the preview feature for that workspace.

But there are many limitations:

Note: Full accessibility isn’t currently supported for data model editing in the Power BI service.

For more details, you could read related document: Edit data models in the Power BI service (preview) - Power BI | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[RobSym87]

Hi Ibendlin,

it's nothing too complex, the data in the column looks like this:

Total

10

15

60

50

54

etc. it's just a total of 6 different section all that have a maximum score of 10 meaning the total value can only go up to 60. That's all there is to it

[lbendlin]

sorry, that is not clear to me. Looks like you dropped some of the details from your original post. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.