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).
jcastr02bhanu_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