Dax code to scale Your variables into ranges dynamically in Power BI
Why is this important?
Often users download datasets with variables on drastically different scales. For example, a snapshot of sales information may contain dollar sales in the millions and GMROI values in single digits.
Analysts have several different scenarios where they need to scale or transform a range of values to another predefined range.
1. Creating a composite value where values won’t have undue impact purely due to scale
2. Creating values for a mixed integer linear programming problem where different magnitudes can lead to suboptimal solutions
3. Generate datasets for AI/Machine Leaning ingestion where the algorithm is sensitive to orders of different magnitudes
This blog post will cover how to write the Dax to scale a range of values that we could modify for use in a larger query or another measure. The code will use the following pattern:
X_scaled = ( Upper Bound - Lower Bound ) × ( x - min(x) ) / ( max(x) - min(x) ) + Lower Bound
Dax code and Power BI Report download
The Dax code to complete this pattern is listed below with comments for each step. We can change the variables MinScale, MaxScale to give us any range we want. Users could create a bound of 0 and 1 or whatever we need for a given visualization/algorithm.
Creating a variable using “Allselected( 'Sample Data'[SKU] )” enables us to scale dynamically based on the items selected through a filter.
Hypothetically, if we were using this pattern in a query to create a CSV for AI/ML input, we could replace this line with the code to grab a list of like items or a different peer group. (Future blog post!)
Dax Code to Scale Sales Values =
VAR MinScale = -1 -- Our Lower Bound
VAR MaxScale = 1 -- Our Upper Bound
VAR AllSelectedItems =
-- Grab the list of selected items so we can scale values currently filtered
ALLSELECTED ( 'Sample Data'[SKU] )
VAR MaxSales =
-- Grab the largest sales value in the dataset
"MaxSales", CALCULATE ( SUM ( 'Sample Data'[Sales] ) )
VAR MinSales =
-- Grab the smallest sales value in the dataset
"MinSales", CALCULATE ( SUM ( 'Sample Data'[Sales] ) )
VAR ScaledSales =
( MaxScale - MinScale )
* DIVIDE (
( CALCULATE ( SUM ( 'Sample Data'[Sales] ) ) - MinSales ),
( MaxSales - MinSales )
) + MinScale
IF ( HASONEVALUE ( 'Sample Data'[SKU] ), ScaledSales, BLANK () )
-- Return the value and don't show a result for the grandtotal
Enjoy the article? Want to discuss services Datalore can offer you? Click the button below and send us an email!