@sjoard there is an alternative to paying for Power BI Pro licences for everyone who needs to see the chart. You can achieve what you want to do with 2 flows in Power Automate.
First: go to your SharePoint list settings -> versioning settings and turn on item version history. This is very important because when you modify the Choice column the flow will compare the new version with the previous one.
Next, create a number column with no decimal places, in this example it's called StatusNumber:
Next we need the flow to run when an item in your list is created. So in Power Automate create from blank an instant cloud flow. Your trigger will be the SharePoint "when an item is created".
The first action should be a Switch control. This is a type of condition that just looks at one column and can have multiple "cases". For the On field select from the dynamic content box Status Value. It needs to be Status Value, not just Status because the column with the status is a choice column (Bad, Could be better, Good).
Then add a case for each status value and in the equal field add Bad, Could be better, Good.
Finally, inside each case add a SharePoint "update item" action and set the StatusNumber column to 1, 2 or 3 as appropriate. You can copy actions to your Power Automate clipboard, so click the 3 dots at the top right of the Switch control and select copy to clipboard because you'll need the switch again in your next flow.
In the top left of the flow click on the name and change it to something meaningful and in the top right click Save.
When you create a new item with the Title and Status columns the flow will trigger and will add the appropriate number into the StatusNumber field. Be aware though that it can take a couple of minutes before it starts running.
But you need a second flow because you need to update the StatusNumber column when the status is changed.
In this case your trigger is the SharePoint "when an item or a file is modified":
Next, add a SharePoint "get changes for an item or a file (properties only)" action. For the ID field click in the field and select ID from the dynamic content box. For the Since field click in the field and from the Expressions tab of the dynamic content box paste in the following expression:
sub(int(triggerOutputs()?['body/{VersionNumber}']),1)
It's that expression that compares the current version of the item with the previous one.
Next, add a Condition and click in the left field and select Has Column Changed: Status is equal to true
You can leave the red if no channel empty because we're not interested whether any other column has changed. In the green if yes channel, add an action and from the window that appears click on the My Clipboard tab and select the Switch that you copied there earlier.
In this example I've changed the status of Vera from could be better to bad. The flow has run - again it can take a couple of minutes to start - and the StatusNumber value has been updated:
So with this method you can use the StatusNumber column as the source of the data for the quick chart web part on your SharePoint page:
Rob
Los Gallardos
Microsoft Power Automate Community Super User