Creating matrix

Hi jcastr02 ,

 

To achieve your desired matrix visual i

Power BI, where you categorize Receiving Stores based on their New Volume (Base + Retained) and Retained Sales, you need to create calculated columns and measures that classify stores into specific buckets. The objective is to count how many unique Receiving Stores fall into each category and display this count in a matrix format. Below is a detailed step-by-step guide, including DAX code snippets to help you achieve this.

 

Step 1: Calculate Total Retained Sales and New Volume

Since each Receiving Store can receive sales from multiple Closing Stores, you need to calculate the total Base Sales and Retained Sales for each Receiving Store. To do this, create the following calculated columns using DAX.

1. Base Sales by Receiving Store:

This column sums up the Base Sales for each receiving store across all related closing stores.

Base Sales = 
CALCULATE(
    SUM('Table'[Base Sales]), 
    ALLEXCEPT('Table', 'Table'[Receiving Store])
)

2. Retained Sales by Receiving Store:

This column sums up the Retained Sales received by each receiving store.

Retained Sales = 
CALCULATE(
    SUM('Table'[Retained Sales]), 
    ALLEXCEPT('Table', 'Table'[Receiving Store])
)

3. New Volume (Base + Retained):

Finally, calculate the total New Volume for each receiving store by adding the Base Sales and Retained Sales.

New Volume = 
[Base Sales] + [Retained Sales]

This calculation ensures that each receiving store has a single figure representing the new total sales they will handle after absorbing volume from closing stores.

Step 2: Create Buckets for Retained Sales and New Volume

To group the Retained Sales and New Volume into ranges for categorization, create two calculated columns to define the buckets.

1. Retained Sales Bucket:

This column categorizes the retained sales into ranges (e.g., 0-30, 30-150, 150+).

Retained Sales Bucket = 
SWITCH(
    TRUE(),
    [Retained Sales] <= 30, "0-30",
    [Retained Sales] <= 150, "30-150",
    "150+"
)

2. New Volume Bucket:

 

This column categorizes the new volume into ranges (e.g., 0-200, 201-350, 351+).

New Volume Bucket = 
SWITCH(
    TRUE(),
    [New Volume] <= 200, "0-200",
    [New Volume] <= 350, "201-350",
    "351+"
)

Step 3: Create a Measure to Count Unique Receiving Stores

Once you have categorized the receiving stores into buckets, you need to create a measure that counts the number of unique Receiving Stores in each bucket.

Unique Receiving Stores:

This measure counts the distinct number of receiving stores within the current filter context.

Unique Receiving Stores = 
DISTINCTCOUNT('Table'[Receiving Store])

Step 4: Build the Matrix Visual

With the calculated columns and measure in place, you can now build the matrix visual in Power BI.

• Rows: Use the Retained Sales Bucket column.
• Columns: Use the New Volume Bucket column.
• Values: Use the Unique Receiving Stores measure.

The matrix will dynamically display the count of unique receiving stores for each combination of retained sales and new volume buckets.

Expected Output in Power BI:

	0-200	201-350	351+
0-30	0	0	0
30-150	2	1	0
150+	0	0	0

Explanation of the Matrix:

In this matrix, you can see the number of unique Receiving Stores categorized based on their New Volume (Base + Retained) and Retained Sales. For example, under the 30-150 Retained Sales and 201-350 New Volume bucket, you have 1 unique receiving store. This matrix provides a clear, categorized breakdown of how the volume from closing stores is distributed across receiving stores.

 

Best regards,