Power BI – Dynamic Benchmarking

[Poweraegg]

Blog: https://poweraegg.com/power-bi-dynamic-benchmarking/

Files: https://github.com/poweraegg/Power-BI

For this video we will look at benchmarking business years for sales and profit. We will look at modelling the data so that we can compare values by creating references to our sales and date table. Both fact tables however will share the same dimensions allowing us to dynamically chose the context for our benchmarking calculations.

Business Case Requirements

• A table for sales and a table for profit, comparing a selected year with another selected year and showing the differences.
• The table must allow us to dynamically choose what context we want to calculate our values on.

• A bar chart for profit and a bar chart for sales showing the sales or profit values for the selected year and the comparison year.

• A context filter allowing us to change the dimensions and two year filters for the benchmarking.

Data Model Requirements

• A sales fact table and a reference to the fact table.
• A date table and a reference to the date table.
• Shared dimensions between the fact table and the benchmarking fact table.
• Inactive relationships between the sales and date table, and the sales benchmarking and date benchmarking table.

Measure Requirements

• A sales fact table and a reference to the fact table.
• Sales and profit measures.
  • For sales table – with userelationship().
  • For benchmarking sales table – with userelationship().
• Delta measures.

Solution Review

• Pros
  • Ability to dynamically compare sales or profit values between two years dynamically.
  • Possibility to change context/dimensions/granularity dynamically so that measure are calculated in a given context.
• Cons
  • References to sales fact table and date dimension table has to be made.
  • Performance may suffer on large dataloads.

Credit: PowerÆgg | Nadim Abou-Khalil