Creating matrix

[jcastr02]

I'm trying to create a matrix counting how many unique Recieving store numbers fall into different categories of their new volume (Base + Retained).  For each closing store - there will be a recieving store (sometimes the recieving store may be recieiving for multiple closing stores.)  .  For Example Recieving Store 4136 has base sales of 190, but now will receive volume from 4 different stores - (10+33+37+68)=148 and with their base sale it would be 148+190=338;  See below current vs. what I'm trying to achieve

current:

Closing Store #	Receiving Store	Base Sales	Retained Sales
4198	4136	190	10
6706	4136	190	33
6613	4136	190	37
5374	4136	190	68
1234	5555	125	9
5678	5555	125	18
8888	9999	75	1
1700	9999	75	10
1800	9999	75	5

Desired Visual

Sales post - Unique number of recieving Stores

		New Volume (Base+Retained)
		0-200	201-350	351+
Retained Sales	0-30	0	0	0
30-150	2	1	0
150+	0	0	0

[DataNinja777]

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,

[bhanu_gautam · Accepted solution]

@jcastr02 , Create a new table to calculate the total retained sales and new volume for each receiving store:

AggregatedData =
SUMMARIZE(
'YourTable',
'YourTable'[Receiving Store],
"TotalBaseSales", MAX('YourTable'[Base Sales]),
"TotalRetainedSales", SUM('YourTable'[Retained Sales]),
"NewVolume", MAX('YourTable'[Base Sales]) + SUM('YourTable'[Retained Sales])
)

Create calculated columns to categorize the new volume and retained sales:

NewVolumeCategory =
SWITCH(
TRUE(),
[NewVolume] <= 200, "0-200",
[NewVolume] <= 350, "201-350",
"351+"
)

RetainedSalesCategory =
SWITCH(
TRUE(),
[TotalRetainedSales] <= 30, "0-30",
[TotalRetainedSales] <= 150, "30-150",
"150+"
)

Insert a Matrix Visual in Power BI.

Add Rows and Columns:

Drag RetainedSalesCategory to the Rows.
Drag NewVolumeCategory to the Columns.
Add Values:

Drag Receiving Store to the Values and set the aggregation to Count (Distinct).

[jcastr02]

@bhanu_gautam  Thank you this worked great! In my original table each month I add new rows with the data (sometimes the data varies month to month)  I was trying to think of a way to be able to put a reporting month filter on my page or is there a way to bring over that column into the AggregatedData table?

Closing Store #	Receiving Store	Base Sales	Retained Sales	Reporting Month
4198	4136	190	10	1/1/2025
6706	4136	190	33	1/1/2025
6613	4136	190	37	1/1/2025
5374	4136	190	68	1/1/2025
1234	5555	125	9	1/1/2025
5678	5555	125	18	1/1/2025
8888	9999	75	1	1/1/2025
1700	9999	75	10	1/1/2025
1800	9999	75	5	1/1/2025
4198	4136	190	10	2/1/2025
6706	4136	190	40	2/1/2025
6613	4136	190	37	2/1/2025
5374	4136	190	90	2/1/2025
1234	5555	125	9	2/1/2025
5678	5555	125	18	2/1/2025
8888	9999	75	1	2/1/2025
1700	9999	75	10	2/1/2025
1800	9999	75	2	2/1/2025