This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Dynamic comparison calculations in DAX
This blogs shows a clever way in which you can use a slicer to choose which values to compare in a measure.

Posted by Sam Lowrie on 08 November 2019

Interactive visual calculations in Power BI

Comparing performance across any dimension can be messy, as if you're not careful you'll require multiple visuals (one for each element of the dimension table whose values you're comparing). In this blog we will let our end user decide for which year to compare sales:

Check if this is the effect you want by clicking here to try the report.

The first thing to do is create a table with a unique list of the values you are interested in. This can be a static table (for which you type in data) or a live connection:

This example is using a query to the same source as my data, but with the duplicates and extra columns removed. I've called the table Live Slicer table.

If you try to use the same model table or column for the slicer as for the data in the visual then the visual will be filtered (which we don't want)! Your data model should thus include a disconnected slicer table, like this:

The Purchase table contains all of the sales data, while the Calendar table splits the dates into Year, Month etc. Notice that the slicer table is separate: it doesn't directly filter anything.

Now to create a slicer with the column from the Live slicer table table (and a visual with the same column from the data table):

The results aren't very exciting without a measure to tie the visuals together!

Now create a measure to calculate the total sales for the column year, but subtracting the selected year's sales:

Comparison to selected =

VAR SelectedValue =

-- Returns the year chosen in the slicer table

VALUES('Live slicer table'[YearNumber])

VAR OriginalColumnValue =

-- Total sales for each year for the filter context

SUM(Purchase[Quantity])

VAR TheSlicerCalculation =

-- Replace the row/column filter with the year chosen by the user

CALCULATE(

SUM(Purchase[Quantity]),

'Calendar'[YearNumber] = SelectedValue

)

-- Take the sales of the year chosen by the user away

-- from the current column/row sales

Return OriginalColumnValue - TheSlicerCalculation

Adding this measure to the visual gives the desired effect:

Power BI DAX Dynamic Measures Calculations

All that is left to do is change the titles - and perhaps add some conditional formatting.

The title measure works using the same idea as the calculation above. Use VALUES to return the only year that is left:

Chart Title =

-- The "" indicate literal text to be joined

-- on to the remaining year

"Year sales less " &

VALUES('Live slicer table'[YearNumber]) & " sales."

This can be applied by turning the Title property to ON and then hovering to the right of the text box and clicking the dots that appear.

Choose the measure Chart Title as the Title text field.

This trick also works for creating slicers to change the aggregate being used: swap between SUM, AVERAGE, MIN or MAX to get truly dynamic visuals!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Interactive visual calculations in Power BI

Head office

London

Manchester