Matrix of LY category vs Curretn Year category

[kk_shp_user]

I have sales data in the below format - 50,000 reps with monthly sales data. It has sales category for each month.

I want to create a matrix visual to show how the reps switched categories between the two years on a month wise basis. Basically the below visual which will show the count of reps.

How can this be achieved?

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached PBI file.

Hope this helps.

[v-rzhou-msft · Accepted solution]

Hi @kk_shp_user ,

You may refer to my sample to learn more details.

My Sample:

Firstly we need to create two unrelated category table for matrix rows and columns.

LY CATEDORY = VALUES('Table'[sales_category])

TY CATEDORY = VALUES('Table'[sales_category])

Also, we need a date table for slicer.

DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"),"month_ending",EOMONTH([Date],0))

Measure:

Measure = 
VAR _CatLY = MAX('LY CATEDORY'[sales_category])
VAR _CatTY = MAX('TY CATEDORY'[sales_category])
VAR _Transform = FILTER(SUMMARIZE('Table',[Rep_id],"LY",CALCULATE(MAX('Table'[sales_category]),FILTER('Table',YEAR('Table'[month_ending]) = 2023)),"TY",CALCULATE(MAX('Table'[sales_category]),FILTER('Table',YEAR('Table'[month_ending]) = 2024))),[LY]<>BLANK() && [TY]<>BLANK())
RETURN
CONCATENATEX(SUMMARIZE(FILTER(_Transform,_CatLY = [LY] && _CatTY = [TY]),[Rep_id]),[Rep_id],",")

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rzhou-msft · Accepted solution]

Hi @kk_shp_user ,

You may refer to my sample to learn more details.

My Sample:

Firstly we need to create two unrelated category table for matrix rows and columns.

LY CATEDORY = VALUES('Table'[sales_category])

TY CATEDORY = VALUES('Table'[sales_category])

Also, we need a date table for slicer.

DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"),"month_ending",EOMONTH([Date],0))

Measure:

Measure = 
VAR _CatLY = MAX('LY CATEDORY'[sales_category])
VAR _CatTY = MAX('TY CATEDORY'[sales_category])
VAR _Transform = FILTER(SUMMARIZE('Table',[Rep_id],"LY",CALCULATE(MAX('Table'[sales_category]),FILTER('Table',YEAR('Table'[month_ending]) = 2023)),"TY",CALCULATE(MAX('Table'[sales_category]),FILTER('Table',YEAR('Table'[month_ending]) = 2024))),[LY]<>BLANK() && [TY]<>BLANK())
RETURN
CONCATENATEX(SUMMARIZE(FILTER(_Transform,_CatLY = [LY] && _CatTY = [TY]),[Rep_id]),[Rep_id],",")

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kk_shp_user · Accepted solution]

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached PBI file.

Hope this helps.

[kk_shp_user]

Thanks for your response but unfortunately I cannot download external files.

[rajendraongole1]

Hi @kk_shp_user Can you create the measure below to calculates the count of reps who switched categories between the current year and the previous year for the current month

Reps_Switched_Categories =
CALCULATE(
DISTINCTCOUNT('Sales'[RepID]),
FILTER(
ALL('Sales'),
CALCULATE(DISTINCTCOUNT('Sales'[Sales_category]), 'Sales'[Year] = YEAR(TODAY()))
<> CALCULATE(DISTINCTCOUNT('Sales'[Sales_category]), 'Sales'[Year] = YEAR(TODAY()) - 1)
&& 'Sales'[Month] = MONTH(TODAY())
)
)

you can change the table name/column names as per your model

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[kk_shp_user]

Thanks but what if I want to show the counts for those who haven't changes as well. Ex: those who were in Cat 1 LY and CY