Create a Dynamic Excel Dashboard with Power Query Calculations
Question details
Create a refreshable Excel dashboard showing weighted averages, daily currency balances, and converted values.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user wants to summarize transaction data by date, currency, transaction type, and region and present the results in an interactive dashboard.
- Observed behavior
- The desired dashboard should update its calculations and visualizations when the source data is refreshed.
Format the transaction data as an Excel table with dates, currencies, quantities, rates, amounts, transaction types, and regions. Prepare a separate exchange-rate table with dates, currency pairs, and clearly defined rate directions, and identify any opening balances.
Calculate and summarize transactions in Power Query
Use Power Query to prepare weighted rates, daily balances, and converted amounts for the dashboard.
A weighted average is the sum of quantity multiplied by rate divided by the sum of quantity. Keep currencies separate when calculating balances, and use the exchange rate applicable to each transaction or reporting date.
Confirm what the DL currency code represents and its conversion rule before using it. Do not assume that its rate direction matches the EUR-to-USD rate.
Select the transaction table and choose Data > From Table/Range. Repeat for the exchange-rate table, then check the date, text, and numeric data types in Power Query Editor.
In the transaction query, choose Add Column > Custom Column and create a weighted-value column using [Quantity] * [Rate]. Use Home > Group By, select Advanced, and group by date, currency, transaction type, and region. Sum both Quantity and the weighted-value column.
Choose Add Column > Custom Column and divide the grouped weighted-value total by the grouped quantity total. Handle zero total quantity separately, for example with an if expression that returns null.
In a separate transaction query, use Home > Group By to sum signed amounts by date, currency, and region. Group the daily rows by currency and region using the All Rows operation. Within each nested table, sort by date, add a zero-based index, and calculate the cumulative sum of daily amounts through each index. Add the applicable opening balance, then expand the nested results.
Use Home > Merge Queries to join the daily results to the exchange-rate table by date and the appropriate currency pair. Expand the rate column and use Add Column > Custom Column to convert EUR amounts to USD using the rate direction supplied. Check for unmatched dates and apply the confirmed rule for DL separately.
Choose Home > Close & Load or Close & Load To to load the summarized queries into Excel tables or the Data Model for reporting.

Build and refresh the Excel dashboard
Turn the prepared query results into interactive summaries and charts.
Explore spreadsheet dashboards with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel files, making it easier to move existing workbooks. It does not fix Microsoft Excel Power Query issues, so check your workbook's query and dashboard compatibility before migrating.

Frequently Asked Questions
How do I calculate a weighted average in Power Query?
Use Add Column > Custom Column to multiply quantity by rate. Then use Home > Group By to sum that result and the quantity for each group, and divide the two totals. Return null or another appropriate value when total quantity is zero.
How do I keep running balances separate for each currency?
First group signed transactions into daily totals by currency and any other balance dimension, such as region. Then group by those balance dimensions with All Rows, sort each nested table by date, and calculate its cumulative total separately.
Which exchange rate should I use to convert EUR to USD?
Use the rate applicable to the transaction or reporting date, according to your reporting rules. If the rate is quoted as USD per EUR, multiply the EUR amount by that rate; if it is quoted in the opposite direction, use the corresponding inverse calculation.
Why does my dashboard not include new transactions?
Make sure the new rows are inside the source Excel table, then choose Data > Refresh All. If the data comes from an external source, also check the query's source settings and refresh any connected PivotTables.
Can one slicer filter multiple PivotTables?
Often, yes. Select the slicer and open Report Connections to choose compatible PivotTables that share the same underlying source or Data Model.




