{"id":2173,"date":"2025-08-07T13:57:35","date_gmt":"2025-08-07T03:57:35","guid":{"rendered":"https:\/\/www.dynamicwebtraining.com.au\/blog\/?p=2173"},"modified":"2025-08-07T13:57:39","modified_gmt":"2025-08-07T03:57:39","slug":"power-bi-goals-scorecard","status":"publish","type":"post","link":"https:\/\/www.dynamicwebtraining.com.au\/blog\/power-bi-goals-scorecard","title":{"rendered":"Building a Live Power BI Goals Scorecard with Real-Time Earthquake Data"},"content":{"rendered":"\n

Power BI\u2019s Goals (Metrics) feature enables data analysts to track key metrics in real-time and automate alerts when thresholds are crossed. In this blog, we will walk through creating a live, automated Power BI Goals scorecard using real-time earthquake data from the USGS<\/a>. <\/p>\n\n\n\n\n\n\n\n

We\u2019ll use the USGS<\/a> daily earthquake CSV feed (updated every minute), build a Power BI report with earthquake metrics, publish it with scheduled refresh, create a Goals scorecard for monitoring those metrics, and set up Teams alerts via Power Automate for proactive notifications.<\/p>\n\n\n\n

By the end, you’ll see how combining real-time data with Power BI Goals and automation can provide proactive insights \u2013 allowing you to monitor critical metrics (like earthquake occurrences) in one place and get alerted as soon as something significant happens.<\/p>\n\n\n\n

1. Connecting to the USGS Real-Time Earthquake Data<\/h2>\n\n\n\n

Our data source is the USGS \u201cAll Earthquakes, Past Day\u201d feed, a publicly available CSV updated every minute with all earthquakes from the last 24 hours. This feed includes fields such as time of the quake, latitude, longitude, status, magnitude and more<\/strong>. We will import this live data into Power BI Desktop:<\/p>\n\n\n\n

i.<\/strong> In Power BI Desktop, go to Get Data > Web. In the URL field, paste the USGS CSV link:<\/p>\n\n\n\n

https:\/\/earthquake.usgs.gov\/earthquakes\/feed\/v1.0\/summary\/all_day.csv<\/a><\/p>\n\n\n\n

Click OK and allow Power BI to connect. It will detect the CSV format and show a preview of the data.<\/p>\n\n\n\n

\"1<\/figure>\n\n\n\n

ii.<\/strong> Click Transform Data to open the Power Query Editor data transformations.<\/p>\n\n\n\n

The feed provides a rolling 24-hour snapshot of earthquakes, so no explicit filtering by date is needed. However, you might choose to remove any fields you won\u2019t use (to simplify the model). For our purposes, we\u2019ll keep key fields like time, latitude, longitude, mag (magnitude), and place. Click Close & Apply to load the data into Power BI. Rename the table as Earthquakes.<\/p>\n\n\n\n

At this stage, you have a live dataset of all earthquakes in the past day. Each refresh will pull the latest earthquakes reported by USGS. Next, we\u2019ll enhance this data with some calculated columns for easier analysis.<\/p>\n\n\n\n

2. Adding Calculated Columns for Magnitude Classification<\/h2>\n\n\n\n

To derive insights, we\u2019ll create two DAX calculated columns in the Earthquakes table:<\/p>\n\n\n\n

(i). MagnitudeBand:<\/strong> a categorical band for earthquake magnitude. This helps in creating charts by magnitude range.<\/p>\n\n\n\n

\u2022 In the Data pane, select the Earthquakes table, click on New Column in the ribbon. Enter the DAX formula for MagnitudeBand. We use a SWITCH(TRUE()) pattern to bin the mag value into ranges:<\/p>\n\n\n\n

MagnitudeBand =\nSWITCH (\n    TRUE (),\n    'Earthquakes'[mag] >= 6, \"6+\",\n    'Earthquakes'[mag] >= 5, \"5\u20136\",\n    \/ else \/\n    \"Sub\u20115\"\n)<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
(ii). IsHighMag:<\/strong> a flag to mark high-magnitude quakes (i.e., magnitude \u2265 6.0) which we consider significant.<\/p>\n\n\n\n

\u2022 Create the IsHighMag column with the following DAX formula to flag quakes of magnitude 6.0 or greater:<\/p>\n\n\n\n
IsHighMag =\nIF ( 'Earthquakes'[mag] >= 6, 1, 0 )<\/code><\/pre>\n\n\n\n
These DAX columns will enable us to easily count total quakes and high-magnitude quakes, and to break down earthquakes by magnitude range in visuals.<\/p>\n\n\n\n
3. Building the Earthquake Report in Power BI Desktop<\/h2>\n\n\n\n
Now that our data model is ready, we can build a simple report to visualize key metrics:<\/p>\n\n\n\n
(i). A Card visual to display total number of earthquakes (in the past 24 hours).<\/p>\n\n\n\n
\u2022 Click on Earthquakes tables and create a measure and use it in the card.<\/p>\n\n\n\n
TotalQuakes =\nCOUNTROWS ( Earthquakes )<\/code><\/pre>\n\n\n\n
(ii). A Card visual to display count of high-magnitude (\u22656.0) earthquakes.<\/p>\n\n\n\n
\u2022 Create the following measure and use it in the card.<\/p>\n\n\n\n
High\u2011Mag (\u22656.0) =\nSUM ( Earthquakes[IsHighMag] ) <\/code><\/pre>\n\n\n\n
(iii). A Card visual to display the value of maximum magnitude earthquakes.<\/p>\n\n\n\n
\u2022 Use this DAX for this card.<\/p>\n\n\n\n
Max mag card =\nMAX ( Earthquakes[mag] )<\/code><\/pre>\n\n\n\n
(iv). A Column chart to show the distribution of quakes by magnitude band.<\/p>\n\n\n\n
\u2022 Insert a Clustered Column Chart. Set the y-axis to the MagnitudeBand column, and the x-axis to Total quakes.<\/p>\n\n\n\n
(v). A Map visual to plot earthquake locations geographically.<\/p>\n\n\n\n
\u2022 Add a Map visual (the basic Map). Drag place to place field well. Drag mag into Size to control bubble size. Make sure the data category of place field is set appropriately. The map will plot all earthquake locations from the last 24 hours.
<\/p>\n\n\n\n
With these visuals, your Power BI report provides a snapshot of the current day\u2019s earthquake activity: total count, count of big quakes, a breakdown by severity, and a map of where they occurred<\/strong>.<\/p>\n\n\n\n
\"2.<\/figure>\n\n\n\n
4. Publishing to Power BI Service and Scheduling Refresh<\/h2>\n\n\n\n
The next step is to publish it to the Power BI Service and set up an automated refresh so that the data (and thus the goals we\u2019ll create) stay up to date:<\/p>\n\n\n\n
(i). Publish the Report:<\/strong> Click Publish on the Home ribbon in Power BI Desktop. Sign in to the Power BI Service. Choose a workspace. After publishing, go to the Power BI Service<\/a> and verify that you see the dataset and report in your workspace.<\/p>\n\n\n\n
(ii). Configure Data Source Credentials:<\/strong> In the Power BI Service, navigate to the dataset corresponding to your report. Click the ellipsis (\u2026) > Settings. Open Data source credentials. Click Edit credentials. For the USGS public feed, choose Anonymous authentication and Privacy level = Public, then sign in or confirm. This grants Power BI Service access to pull the data.<\/p>\n\n\n\n
(iii). Scheduled Refresh:<\/strong> Because the USGS feed updates every minute, you may want frequent refreshes. However, in Power BI Pro, the maximum is 8 refreshes per day. Set a schedule (e.g., every 3 hours from 00:00 to 23:00). If you have a Premium workspace or Premium Per User, you could refresh more often; but for our purposes, 8 refreshes are adequate to get near-real-time data. Ensure the time zone is correct and apply the settings.<\/p>\n\n\n\n
\"3<\/figure>\n\n\n\n
With scheduled refresh in place, the published dataset will keep updating, and by extension, any connected Goals metrics will update as well (since Goals pulls from the dataset\u2019s latest value). Now we\u2019re ready to create the Goals scorecard.<\/p>\n\n\n\n
5. Creating a Power BI Goals Scorecard and Metrics<\/h2>\n\n\n\n
Microsoft\u2019s Power BI Goals (also called Metrics in the navigation pane) allows you to create a scorecard \u2013 essentially a dashboard of KPIs \u2013 and tie each metric to data from reports. We will create a scorecard to track our earthquake metrics:<\/p>\n\n\n\n
(i). Open the Metrics Hub:<\/strong> In Power BI Service, click on Metrics in the left navigation pane. This opens the Metrics hub. If you haven\u2019t used Goals before, it might be empty or show sample scorecards.<\/p>\n\n\n\n
\"4<\/figure>\n\n\n\n
\"5.<\/figure>\n\n\n\n
(ii). Create a New Scorecard:<\/strong> Click New scorecard. Name it “Earthquake Monitoring Scorecard<\/strong>“.<\/p>\n\n\n\n
\"6<\/figure>\n\n\n\n
or<\/p>\n\n\n\n
You may create new scorecards from your workspaces like this:<\/p>\n\n\n\n
\"7<\/figure>\n\n\n\n
(iii). Add Metrics (Goals):<\/strong> On your new empty scorecard, click \u201cNew+<\/strong>” to add a goal\/metric. For the first metric: Name it \u201cTotal Quakes (24h)<\/strong>“. For Current value, select Connect to data. Navigate to All reports tab and find the report you just published. Click on next. Now, click on the card visual that shows total quakes and then click on Connect (Power BI will capture the visual\u2019s value).<\/p>\n\n\n\n
\"8<\/figure>\n\n\n\n
For Target value, you can set a static target manually. For demonstration, let\u2019s say we set Target = 400 quakes (just an arbitrary benchmark for \u201cnormal” daily quake count). You could also connect the target to another data point if you had one, but we\u2019ll use a static number. Assign an Owner (could be yourself or a generic user for now) \u2013 owners receive updates and are responsible for the metric. Click on Save.<\/p>\n\n\n\n
(iv). Add the second metric for “High-Magnitude Quakes<\/strong>“: Connect its current value to the high-magnitude quakes card visual. This is 0 on most days if no 6.0+ quake occurred, or some small number. In this case, set a Target = 0 (since ideally, we don\u2019t want any major quakes).<\/p>\n\n\n\n
\"9<\/figure>\n\n\n\n
6. Defining Targets and Automatic Status Rules<\/h2>\n\n\n\n
By default, after adding metrics, you can manually change a goal\u2019s status or set up rules. We want automated status updates:<\/p>\n\n\n\n
(i). Edit Status Rules:<\/strong> Enter Edit mode on the scorecard (there\u2019s an Edit button or Read mode toggle on the scorecard page).<\/p>\n\n\n\n
\"10<\/figure>\n\n\n\n
In Edit mode go to settings and set your scorecards rules as shown and save them:<\/p>\n\n\n\n
\"11<\/figure>\n\n\n\n
(ii). For the Total Quakes (24h) metric, click the “edit this goal<\/strong>” next to it and choose “set up rules<\/strong>” under status section. This opens a pane for status rules.<\/p>\n\n\n\n
If Current value > Target (meaning more quakes than our threshold), then status = Critical (indicating higher than usual activity).<\/p>\n\n\n\n
\"12<\/figure>\n\n\n\n
(iii). Similarly, Add Rule for High-Magnitude Quakes<\/strong>. If Current value > 0 (i.e., one or more high magnitude quakes occurred), then status = Critical. If value = 0, status = Normal (all clear). Save the rule.<\/p>\n\n\n\n
\"13<\/figure>\n\n\n\n
Power BI Goals\u2019 status rules are quite flexible \u2013 you can base them on the numeric value, percent of target achieved, or even date. In our case, simple value comparisons suffice. These rules will now automatically update the colour of the metric whenever the data refreshes and the condition is met.<\/p>\n\n\n\n
7. Enabling Trendlines and Historical Snapshots<\/h2>\n\n\n\n
One powerful aspect of Power BI Goals is the ability to see trends over time for each metric. When metrics are connected to data and regularly refreshed, Power BI captures snapshots of the value on a daily cadence by default. These snapshots allow Goals to display a sparkline (trendline) for the metric and to maintain a history of how the metric has changed.<\/p>\n\n\n\n
To ensure you\u2019re capturing history:<\/p>\n\n\n\n
(i). In our case, our metrics are rolling 24h count, which resets daily. Power BI will take one snapshot per day of the current value. To set up the trendlines, open the “time period<\/strong>” tab and choose cycle over cycle<\/strong> radio button. Set interval as daily and labels them as Daily Values. Display the values ad absolute values. Save the changes. And do these steps for the other metric as well.<\/p>\n\n\n\n
\"14<\/figure>\n\n\n\n
Over a few days of operation, you will start seeing a trendline on the scorecard under the trend column, indicating how the value changes each day. For example, Total Quakes might fluctuate each day (perhaps one day 230 quakes, next day 270, etc.).<\/p>\n\n\n\n
\"15<\/figure>\n\n\n\n
The trendline will plot those daily values so you can visually spot upward or downward trends. This is particularly useful for seeing whether the metric is consistently rising, seasonal patterns, or the impact of events. In our scenario, it could highlight if seismic activity is increasing over days. This is incredibly useful for proactive analysis \u2013 e.g., if the trend shows a steady rise in daily quakes, you might anticipate an “at risk” status soon and investigate why the activity is increasing.<\/p>\n\n\n\n
8. Automating Teams Alerts with Power Automate<\/h2>\n\n\n\n
Having a live scorecard is great, but we can take it a step further: automated alerts<\/strong>. We want Power BI to proactively notify us (or our team) when check-ins are made. This flow setup sends notifications whenever a check-in is logged for a goal \u2014 ideal for data analysts, team leads, or operations heads who need timely updates. Power BI integrates with Power Automate to enable such notifications. Here\u2019s how to set up a Teams alert via a Power Automate flow:<\/p>\n\n\n\n
(i). Copy your Scorecard:<\/strong> Power Automate only supports shared or collaborative workspaces, not “My workspace” so if your scorecard is in “My workspace” then move your scorecard to a shared Workspace. In Power BI Service: Go to the Scorecard inside “My workspace”. Click File \u2192 Copy. Save it into a shared workspace (e.g., one you or your team owns).<\/p>\n\n\n\n
\"16.Copy<\/figure>\n\n\n\n
Mark “include check-in history<\/strong>” check.<\/p>\n\n\n\n
(ii). Create a New Flow:<\/strong> Go to Power Automate<\/a> and sign in. Or click on “Create a flow<\/strong>” from the top action bar.<\/p>\n\n\n\n
\"17<\/figure>\n\n\n\n
(iii). Choose Trigger:<\/strong> Select the trigger “When someone adds or edits a goal check-in “. This trigger will fire every time someone logs a check-in manually or via automation.<\/p>\n\n\n\n
\"18<\/figure>\n\n\n\n
You\u2019ll need to configure:<\/p>\n\n\n\n