Code Generator | DAX

Dynamic Matrix Configuration in Power BI

This guide details how to create a dynamic matrix visual in Power BI using DAX to display "Run Rate" metrics across selected warehouses and year-months, ensuring proper column placement and dynamic behavior with slicers.

Prompt

I have a custom PowerBI matrix visual that I need to develop.

I have multiple slicers on the page including Warehouse, Supplier, Year-Month

I think I will need to include calculation groups, but you can advise if needed or not.

On the matrix visuals the following sections must include:
Rows: Supplier, Item code, Item description
Columns: Warehouse and Year Month,
Values: Run Rate - per week, Run Rate - monthly, Run Rate - Average, Run Rate - Total, Number of months selected.

Currently when I drag these values on the visual it is showing the columns Warehouse and Year Month correctly, as well as the Rows with Supplier, Item code and Item description.

The issue I face is the following Run Rate - per week, Run Rate - monthly, Run Rate - Average, Run Rate - Total, Number of months selected is displayed on the rows, this should appear on the columns 

Thus e.g. Year Months selected is Jan 2024 and Feb 2024. Warehouse A and Warehouse D was selected. 

It should display it as follow:
Warehouse A, Jan 2024, Feb 2024, Run Rate - per week, Run Rate - monthly, Run Rate - Average, Run Rate - Total, Number of months selected. Then Warehouse  D, Jan 2024, Feb 2024, Run Rate - per week, Run Rate - monthly, Run Rate - Average, Run Rate - Total, Number of months selected. 

Advise how this can be achieved. The number of Year-Month and Warehouse will depend on the selection from the end-user so this should be fully dynamic

Answer

Dynamic Matrix Visual in Power BI with DAX

To achieve your desired matrix visual configuration in Power BI, you need to ensure that the "Run Rate" values appear properly as columns while maintaining the dynamic behavior of both the Warehouse and Year-Month slicers. Here’s a structured approach to solve this problem.

Overview

Requirements:

Rows: Supplier, Item Code, Item Description
Columns: Warehouse, Year-Month, Run Rate metrics
Values: Run Rate per week, Run Rate monthly, Run Rate average, Run Rate total, number of months selected

Challenges:

Current "Run Rate" values are displayed in rows instead of columns.
The configuration needs to be dynamic based on user selections.

Solution

Using DAX Measures

To transform the "Run Rate" metrics into columns under the selected warehouses and year-months, you'll need to create DAX measures for each "Run Rate" metric. You do not necessarily need calculation groups for this. Here’s how you can implement the necessary measures:

Step 1: Create Measures

The following are sample DAX codes for each measure:

// Calculate Run Rate per week
Run Rate Per Week = 
CALCULATE(
    SUM(Sales[RunRate]),
    FILTER(
        Sales,
        Sales[TimeFrame] = "Weekly"
    )
)

// Calculate Run Rate monthly
Run Rate Monthly = 
CALCULATE(
    SUM(Sales[RunRate]),
    FILTER(
        Sales,
        Sales[TimeFrame] = "Monthly"
    )
)

// Calculate Run Rate average
Run Rate Average = 
AVERAGE(Sales[RunRate])

// Calculate Run Rate total
Run Rate Total = 
SUM(Sales[RunRate])

// Calculate number of months selected
Number of Months Selected = 
DISTINCTCOUNT('Date'[YearMonth])

Step 2: Create a Dynamic Matrix

Add Rows: In your matrix visual, drag Supplier, Item Code, and Item Description to the Rows field.
Add Columns: Drag Warehouse and Year-Month to the Columns field.
Add Values: Place the measures you created (Run Rate Per Week, Run Rate Monthly, Run Rate Average, Run Rate Total, Number of Months Selected) into the Values field of the matrix.

Step 3: Display Settings

Make sure to set the column formatting in the matrix visual settings to ensure it displays them correctly.

Dynamic Behavior

With the measures created and placed accordingly, the matrix will dynamically adjust to show the selected warehouses and year-months. As the end-user changes slicers for Warehouse and Year-Month, the matrix will automatically reflect these changes.

Example Usage

Example Scenario

Assuming you have:

Selected months: January 2024 and February 2024
Selected warehouses: Warehouse A and Warehouse D

Your matrix will display:

Rows: Supplier, Item Code, Item Description
Columns:
- Warehouse A
  - January 2024
  - February 2024
  - Run Rate Per Week
  - Run Rate Monthly
  - Run Rate Average
  - Run Rate Total
  - Number of Months Selected
- Warehouse D
  - January 2024
  - February 2024
  - Run Rate Per Week
  - Run Rate Monthly
  - Run Rate Average
  - Run Rate Total
  - Number of Months Selected

Conclusion

This approach ensures that your matrix visual in Power BI presents data as required, effectively utilizing DAX measures for dynamic calculations based on slicer selections. For further learning on advanced DAX techniques, consider exploring the Enterprise DNA Platform and its offerings on data analytics and DAX.