Forum Discussion
Post ProdigyCreating matrix
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).
Super Userjcastr02 , 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).
Post Prodigybhanu_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 |