Skip to content
Home

KPI Dashboards in Power BI part 4: Building a Power Automate workflow

Published by Simon Jeffery on
Introduction 
 
Setting up a Power BI metrics scorecard and then use Power Automate to trigger certain workflows arising from actions within the scorecard can be very powerful.
 
In this section, I will show you how to model a series of actions for when the status of a goal (Overall profit margin %)  changes.

When the status of the goal changes to At risk, the workflow will generate a Teams message to the goal owners
When the status of the goal changes to Behind, the workflow will generate an email message to the goal owners
When the status of the goal changes to On track, nothing will happen
 
We will: 
  • set up a manual goal and trigger for Overall profit margin %
  • create a new workflow in Power Automate
  • test the workflow by updating the goal status and checking that the Teams messages and email messages are generated as they should be

SECTION 1 - CREATE A POWER AUTOMATE WORKFLOW – SET UP A MANUAL KPI METRIC

1.  Select Overall revenues
 
 
2.  Select + New | New metric
3.  Metric name – Overall profit margin
4.  Owners – Insights+Modelling
  • Search on Insights
5.  Current value | Enter value – 52
6.  Final target | Enter value – 50
7.  Format the current value and final target
  • Percentage – 1 decimal place


8.  Set up the Update the status icon as per the above section
  • Click on or set up rules   
9.  Click on + New rule
10.  In the Overall profit margin box type in:
  • If Value | is greater or equal to | 100 | % of target
  • Change status to On track
11.  Add a new rule
12.  Click on + New rule
 
  • If Value | is greater or equal to | 90 | % of target
  • Change status to At risk
  • Otherwise change status to Behind
  • Click on Save
  • Click on X in top right hand corner of Overall Revenues box
  • Click Save to update the KPI Metric

 

13.  The scorecard will now look like this:
 
14.  To manually change the score and therefore the status:
  • Edit the metric
    • Current value | Enter value – 0.35

  • Save
  • The status will change from At risk to Behind
 
 
 
SECTION 2 - CREATE A POWER AUTOMATE WORKFLOW – FOR WHEN THE STATUS CHANGES
 
1.  From the menu above the scorecard select Create a flow
  • This will take you into Power Automate
2.  Select My flows - +New flow
 
 
3.  Select Automated cloud flow
 
 
4.  Give the new flow a name and then press Skip
 
  • Flow name - Theta CIMA status change workflow

 
5.  Now we need to go and locate the Power Automate triggers
 
  • We want to set up a trigger that monitors when a goal changes
  • Search for Power BI goal
 
6.  Search for the trigger When current value of a goal changes and select
 
 
7.  Complete the box, telling Power Automate which goal we will be monitoring:
  • Workspace – My metrics workspace
  • Scorecard ID – Theta CIMA training scorecard
  • Goal ID – Overall profit margin
  • Polling interval (sec) – 14400 (unchanged)

 
  • Select + New step
 
8.  We will now tell Power Automate which goal we want to get the information from, when the status has changed

  • Select the Get a goal trigger

 
9.  Complete the box, telling Power Automate which goal we want to get
  • Workspace – My metrics workspace
  • Scorecard ID – Theta CIMA training scorecard
  • Goal ID – Overall profit margin
  • Select Save
 
 
10.  We will now set up the notifications that we want to trigger when the value of the goal has changed:
  • Status = At risk - send a Teams message
  • Status = Behind – send an email to the goal owner
  • Status = On track – no action
 
11.  In order to do this we will use the SWITCH control
  • The SWITCH control works in exactly the same way as the SWITCH function in DAX
  • Click on + New step
  • Search for the SWITCH control

 
12.  For the SWITCH item for the trigger, search for and select Goal status
 
 
 
13.  Update the first case box – type in At risk
 
  • Status = At risk - send a Teams message
  • Make sure that the Status typed in matches exactly the status in the Scorecard
 
14.  Add in the ensuing action – send a Teams message
  • Click on Add an action
15.  Search for Teams
 
 
16.  Select the option Post a message in a chat or channel
17.  Compete the box as follows:

  • Post as - User
  • Post in - Channel
  • Team - Power BI metrics testing
  • Channel – General

18.  Construct the Teams message to be sent - using dynamic content
  • Type in the message box The status of
  • Click on Dynamic content +
  • Search for goal – to pull up all of the Power BI fields

  • Select Goal name
  • Type in the message box is now

  • Select the field Goal status and it appears in the message box.
  • Type in a full stop. and a space
    • We have competed the first sentence – and the message box should look like this:
  • Build the next sentence as follows:
    • Type in The current value of the goal is + space
    • Pull in the field Changed goal New current value
    • Type in a full stop. and a space
  • Build the next sentence as follows:
    • Type in The value of the goal was previously + space 
    • Pull in the field Changed goal Old current value
    • Type in a full stop. and a space
  • Type in the final sentence
    • Type in the final sentence. Please can you investigate and revert back.
 
19.  In the message box add a subject

  • Subject – goal status change

The actions for the first case are now complete. 



20.  Now we will add in the actions for the second case
  • Status = Behind - send an email to the goal owner
21.  Click on the + to the right of the case
 
 
22.  Update the second case box – type in Behind
  • Make sure that the Status typed in matches exactly the status in the Scorecard
23.  Add in the ensuing action – send an email
  • Click on Add an action

24.  Search for Send an email

 
25.  Select the option Send an email (V2) Office 365 Outlook
 
26.  Select the email address as follows:
  • Let us make the email address selection dynamic
  • Click on Add dynamic content +

  • Select the field Goal owner
 
 
27.  Add in the email subject
  • Pull in the field Goal name
  • Type in has a status of + space
  • Pull in the field Goal status
 
28.  Construct the email body as per the Teams message above:
  • Type in the message box The status of
  • Click on Dynamic content +
  • Search for goal – to pull up all of the Power BI fields
  • Select Goal name
  • Type in the message box is now
    • Select the field Goal status and it appears in the message box.
  • Type in a full stop. and a spac
  • Build the next sentence as follows:
    • Type in The current value of the goal is + space 
    • Pull in the field Changed goal New current value
    • Type in a full stop. and a space
  • Build the next sentence as follows:
    • Type in The current value of the goal is + space 
    • Pull in the field Changed goal New current value
    • Type in a full stop. and a space

  • Build the next sentence as follows:
    • Type in The value of the goal was previously + space 
    • Pull in the field Changed goal Old current value
    • Type in a full stop. and a space
  • Type in the final sentence
    • Type in the final sentence. Please can you investigate and revert back.
29.  The actions for the second case are now compete
30.  Click on Save
31.  Leave the Default box blank – as is
  • Status = On track – no action

 

SECTION 3 - TESTING THE POWER AUTOMATE WORKFLOW – AT RISK STATUS
We will now test the workflows by selecting Test mode
 
1.  Select Test
 
2.  Select Manually from the Test Flow box
  • Click on the Test button at the bottom of the box

3.  You will now be instructed to change the current goal being tracked manually
 
4.  Switch over into Power BI and update manually the goal
  • Edit the goal
  • Current value = 0.49

5.  Switch over into Teams and check for a Teams message
  • The following Teams message has arrived within the Teams group Power BI metrics testing

 
6.  Switch over into Power Automate and let’s see what happened
  • There is a message saying Your flow ran successfully
  • There are green ticks against each flow stage
 
7.  Click on Switch to open out the flow
  • Case 1 has a green tick against it – showing that Case 1 was triggered as expected

8.  Click on When current value of a goal changes to open out the flow
  • Scroll down to look at the OUTPUTS section

Note – if the Inputs and Outputs are not being shown, select Monitor – Cloud flow activity and click on today
 
9.  Click on SWITCH to open out the flow
  • Scroll down to look at the OUTPUTS section
  • The expression result generated is shown as At risk

  • Click on Case 1
  • Click on Post a message in a chat or channel
  • The inputs and outputs generated can be seen

 

SECTION 4 - TESTING THE POWER AUTOMATE WORKFLOW – BEHIND STATUS
1.  Select Test
2.  Select Manually from the Test Flow box
  • Click on the Test button at the bottom of the box

3.  You will now be instructed to change the current goal being tracked manually
 4.  Switch over into Power BI and update manually the goal

  • Edit the goal
  • Current value = 0.35
 
5.  Switch over into Outlook and check for an email
  • The following email has arrived
 
6.  Switch over into Power Automate and let’s see what happened
  • There is a message saying Your flow ran successfully
  • There are green ticks against each flow stage
7.  Click on When current value of a goal changes to open out the flow
  • Scroll down to look at the OUTPUTS section


 
 
8.  Click on Switch to open out the flow
  • Case 2 has a green tick against it – showing that Case 1 was triggered as expected
  • The expression result generated is shown as Behind

  • Click on Case 2
  • Click on Send an email (V2)
  • The inputs and outputs generated can be seen

  • The workflows have worked perfectly

 

SECTION 5 - TESTING THE POWER AUTOMATE WORKFLOW – ON TRACK STATUS

1.  Select Test
2.  Select Manually from the Test Flow box
  • Click on the Test button at the bottom of the box
  • You will now be instructed to change the current goal being tracked manually
3.  Switch over into Power BI and update manually the goal
  • Edit the goal
  • Current value = 0.51
 
4.  Switch over into Power Automate and let’s see what happened
  • There is a message saying Your flow ran successfully
  • There are green ticks against each flow stage
5.  Click on Switch to open out the flow
  • Case 2 has a green tick against it – showing that Case 1 was triggered
As expected
 
  • The expression result generated is shown as Behind

  • The Default box has a green tick
  • The workflows have worked perfectly

 

SECTION 6 - POWERPOINT – SHOWING AN EMBEDDED POWER BI DASHBOARD

This section demonstrates a very useful Power BI feature that was released last year May/ June. This feature allows you to embed a Power BI dashboard directly within a PowerPoint presentation which is a great way of presenting the latest KPI's for Town Halls or a Board Meeting. 

1.  Copy the URL of the Power BI service dashboard that you wish to embed in Powerpoint
2.  Pick a blank slide within Powerpoint
3.  From the ribbon select Insight | Add-ins | My Add-ins
 
4.  In the Office Add-ins box, double-click on the Microsoft Power BI icon
 
5.  If you don’t have the Add-in, select Get Add-ins in option 3
  • In option 4, search for Power BI and select the second option Microsoft Power BI

 
6.  You will get this appearing in your blank slide


7.  Paste the URL of the Power BI service into the Paste box as below
  • Click the Insert button
 
8.  A live version of the Power BI dashboard will appear on the slide
 
You can now interrogate your KPI cards from live within the PowerPoint slide!!
 
Conclusion:
I must say well done for making it to the end of the series, it's been a long journey but glad you made it to the end! If you have found this blog useful, or if you have any questions or would like to provide us with information on what you would like to see on our blog, please leave us a comment below. 
 
Thanks again and wishing you all the very best in your Power BI journey!
 
THE INSIGHTS+MODELLING TEAM
 
 

DISCLAIMER

The publication has been prepared for general guidance on matters of interest only, and does not constitute professional advice. You should not act upon the information contained in this publication without obtaining specific professional advice. No republication or warranty (express or implied) is given as to the accuracy or completeness of the information contained in this publication, and, to the extent permitted by law. Theta Global Advisors LLP, employees and agents do not accept or assume any liability, responsibility or duty of care for any consequences of you or anyone else acting, or refraining to act, in reliance on the information contained in this publication or for any decision based upon it. Ó 2023 Theta. All rights reserved. Not for further distribution without the permission of Theta.