to provide count of supplier codes, based on categories of fluctuation and average score

[Prahlad]

I have a table which I have input in power BI( attached at the end).

Here I have data of supplier scores from the months November to April. Each supplier is denoted by the "Supplier Code" which is unique for each month. Now based on scores, certain categorizations were made

• if Average score across all 6 months is > 475, then the supplier comes under High performance category
• if Average score across all 6 months is <=475 and >=400 , then the supplier comes under Moderate performance category 
• if Average score across all 6 months is <=399 and >=250 , then the supplier comes under Low performance category 
• if Average score across all 6 months is <= 249 , then the supplier comes under high risk category 

Supplier wise categorization:

• If a supplier has Average score > 475 and has less fluctuation(using standard deviation) : Consitent High performer
• If a supplier has average score <=250 and has less fluctuation(using standard deviation) : Consistent Low performer
• If a supplier has erractic values with high fluctuation : Major Fluctuation
• If supplier has a decreasing trend in score(Average score - score 3 months back ) : Deteriorating performance
• If supplier Performance has suddenly decreased drastically : Major drop in performance.

Domain wise categorisation:

I would want to have a feature to slice the number of suppliers based on the above categories but also based on domains. since certain domains predominantly will be in range of score and have to be judge relatively.

Thus I would want some help regarding the necessary dax measures to be used and other additional things I might not have considered.

Kindly provide the solution as per 2021 Power BI

here is how I would want my output to look like, to an extent: 

Here is the base data:

Month	Segment	SUPPLIER CODE	DOMAIN	Overall Score (Rounded Off)
Nov`23	MHCV	7200980	NON-METALLIC	450
Nov`23	MHCV	7204096	NON-METALLIC	340
Nov`23	MHCV	7203771	CHASSIS PROP	425
Nov`23	LCV	7205672	CHASSIS PROP	383
Nov`23	MHCV	7200370	CHASSIS PROP	300
Nov`23	MHCV	7205371	CHASSIS PROP	340
Dec`23	MHCV	7200002	BAR MACHINING	375
Dec`23	MHCV	7200004	NON-METALLIC	407
Dec`23	MHCV	7200013	ENGINE PROP	450
Dec`23	E-H1,MHCV	7200015	FORGING	375
Dec`23	E-H1,MHCV	7200016	CASTING	275
Dec`23	MHCV	7200017	ELECTRICAL	425
Jan`24	MHCV	7200002	BAR MACHINING	343
Jan`24	MHCV	7200004	NON-METALLIC	408
Jan`24	MHCV	7200013	ENGINE PROP	500
Jan`24	E-H1,MHCV	7200015	FORGING	350
Jan`24	E-H1,MHCV	7200016	CASTING	347
Jan`24	MHCV	7200017	ELECTRICAL	412
Jan`24	MHCV	7200019	ELECTRICAL	275
Feb`24	MHCV	7200002	BAR MACHINING	369
Feb`24	MHCV	7200004	NON-METALLIC	409
Feb`24	MHCV	7200013	ENGINE PROP	500
Feb`24	E-H1,MHCV	7200015	FORGING	375
Feb`24	E-H1,MHCV	7200016	CASTING	337
Feb`24	MHCV	7200017	ELECTRICAL	455
Feb`24	MHCV	7200019	ELECTRICAL	300
Feb`24	E-H1,MHCV	7200021	CHASSIS PROP	475
Mar`24	MHCV	7200946	RM	375
Mar`24	MHCV	7200004	NON-METALLIC	409
Mar`24	MHCV	7200013	ENGINE PROP	500
Mar`24	E-H1,MHCV	7200015	FORGING	375
Mar`24	E-H1,MHCV	7200016	CASTING	337
Mar`24	MHCV	7200017	ELECTRICAL	455
Mar`24	MHCV	7200019	ELECTRICAL	300
Mar`24	E-H1,MHCV	7200021	CHASSIS PROP	475
Apr`24	MHCV	7200002	BAR MACHINING	322
Apr`24	MHCV	7200004	NON-METALLIC	410
Apr`24	MHCV	7200010	CASTING	475
Apr`24	MHCV	7200013	ENGINE PROP	500
Apr`24	E-H1,MHCV	7200015	FORGING	400
Apr`24	E-H1,MHCV	7200016	CASTING	336
Apr`24	MHCV	7200017	ELECTRICAL	455
Apr`24	MHCV	7200019	ELECTRICAL	300
Apr`24	E-H1,MHCV	7200021	CHASSIS PROP	475
Apr`24	E-H1,LCV,MHCV	7200023	NON-METALLIC	360

@amitchandak @lbendlin @ryan_mayu @Greg_Deckler @Ritaf1983  @_AAndrade 

[lbendlin]

Your sample data is insufficient to determine reliable statistics.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[v-tangjie-msft]

Hi @Prahlad ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

Average Score = AVERAGEX(FILTER(ALLSELECTED('Table'),[Segment]=MAX('Table'[Segment])),[Overall Score (Rounded Off)])

Score 3 months back = AVERAGEX(FILTER(ALLSELECTED('Table'),[Month] in {"Nov`23","Dec`23","Jan`24"} && [Segment]=MAX('Table'[Segment])),[Overall Score (Rounded Off)])

Std Deviation = 
STDEVX.P(FILTER(ALLSELECTED('Table'),[Segment]=MAX('Table'[Segment])),[Overall Score (Rounded Off)])

Category = SWITCH(TRUE(),
[Average Score]>475 && [Std Deviation]<0.5,"Consitent High performer",
[Average Score]<250 && [Std Deviation]<0.5,"Consistent Low performer",
[Average Score] >=475,"High performance category",
[Average Score]<=475 && [Average Score]>=400,"Moderate performance category",
[Average Score]<=399 && [Average Score]>=250,"Low performance category",
[Average Score]<=249,"High risk category",
[Std Deviation]>=1,"Consistent Low performer",
[Average Score]-[Score 3 months back]<0,"Deteriorating performance")

//Here the fluctuation is small, I set the threshold is the standard deviation is less than 0.5, the fluctuation of the threshold is greater than 1, you can change according to your own needs

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Prahlad]

@lbendlin @v-tangjie-msft Thank you for your response

Based on your inputs I worked a little on my problem and found a better way to present it

(This is the first page I want as output)

Basically I have performance of certain suppliers for each month and I want to categorize each supplier based on their monthly performance.

Now Each Domain has an Average score and suppliers of that domain must be measured based on the domain average across months and not the overall average.

The name of the Table I used in "Compiled"

1) Thus I used the following measure: (To calculate the average score for a domain across months)

Domain Average Score = CALCULATE(AVERAGE(Compiled[Overall Score (Rounded Off)]),FILTER(Compiled,[DOMAIN]=[DOMAIN]))

2) To understand the fluctuations in the performance of the supplier, I used standard deviations for each supplier, across months

Supplier Std Dev = CALCULATE(STDEV.S(Compiled[Overall Score (Rounded Off)]),FILTER(('Compiled'),[SUPPLIER NAME]=[SUPPLIER NAME]))

3) I calculated the average score got by each supplier across months in order to help me better categorize them

Supplier Average Score = CALCULATE(AVERAGE(Compiled[Overall Score (Rounded Off)]),FILTER(Compiled,Compiled[SUPPLIER NAME]=Compiled[SUPPLIER NAME]))

Using the above measures I would want have a Distinct count of Suppliers and also a list of the suppliers that fall into the following categories:

1) Consistent High performer: Where Std Dev is low and the Supplier Average Score >= 0.95 * Domain Average Score

2) Consistent Moderate performer: Where Std Dev is low and the Supplier Average Score < 0.95 * Domain Average Score && >=0.7*Domain Average Score

3) Consistent low performer: Where Std Dev is low and the Supplier Average Score <= 0.69 * Domain Average Score && >=0.5*Domain Average Score

4) Consistent High Risk Supplier: Where Std Dev is low and the Supplier Average Score <0.5 * Domain Average Score

5) Major Drop in performance: (Here I'm not sure which Logic will work the best so I have 3 options, and would love to have your help as to which one will give the best results)

   a) Where Std Dev is high and the Supplier Average Score <0.5 * Domain Average Score

   b) Where Std Dev is high and the Supplier Current Score < Median of Supplier Score 

   c) Where Std Dev is high and the Supplier Current Score < Supplier Score 3 months back.

Using the above logic I would want the following as the second page for output:

Now why I want the list of Suppliers as per categories are beacause I have made a drill-through for each supplier to understand their performance in detail:

Below is the data I used for this.

(Also Kindly let me know, specifically what additional data do you need for the above required functionalities )

Month	Segment	SUPPLIER CODE	DOMAIN	Overall Score (Rounded Off)
Nov`23	MHCV	7200004	NON-METALLIC	407
Nov`23	MHCV	7200002	BAR MACHINING	375
Nov`23	E-H1,MHCV	7200015	FORGING	375
Nov`23	MHCV	7200019	ELECTRICAL	300
Nov`23	MHCV	7200017	ELECTRICAL	425
Nov`23	E-H1,MHCV	7200021	CHASSIS PROP	425
Nov`23	E-H1,MHCV	7200016	CASTING	350
Dec`23	MHCV	7200002	BAR MACHINING	375
Dec`23	MHCV	7200004	NON-METALLIC	407
Dec`23	MHCV	7200013	ENGINE PROP	450
Dec`23	E-H1,MHCV	7200015	FORGING	375
Dec`23	E-H1,MHCV	7200016	CASTING	275
Dec`23	MHCV	7200017	ELECTRICAL	425
Dec`23	MHCV	7200019	ELECTRICAL	300
Dec`23	E-H1,MHCV	7200021	CHASSIS PROP	425
Jan`24	MHCV	7200002	BAR MACHINING	343
Jan`24	MHCV	7200004	NON-METALLIC	408
Jan`24	MHCV	7200013	ENGINE PROP	500
Jan`24	E-H1,MHCV	7200015	FORGING	350
Jan`24	E-H1,MHCV	7200016	CASTING	347
Jan`24	MHCV	7200017	ELECTRICAL	412
Jan`24	MHCV	7200019	ELECTRICAL	275
Jan`24	E-H1,MHCV	7200021	CHASSIS PROP	471
Feb`24	MHCV	7200002	BAR MACHINING	369
Feb`24	MHCV	7200004	NON-METALLIC	409
Feb`24	MHCV	7200013	ENGINE PROP	500
Feb`24	E-H1,MHCV	7200015	FORGING	375
Feb`24	E-H1,MHCV	7200016	CASTING	337
Feb`24	MHCV	7200017	ELECTRICAL	455
Feb`24	MHCV	7200019	ELECTRICAL	300
Feb`24	E-H1,MHCV	7200021	CHASSIS PROP	475
Mar`24	MHCV	7200004	NON-METALLIC	409
Mar`24	MHCV	7200013	ENGINE PROP	500
Mar`24	E-H1,MHCV	7200015	FORGING	375
Mar`24	E-H1,MHCV	7200016	CASTING	337
Mar`24	MHCV	7200017	ELECTRICAL	455
Mar`24	MHCV	7200019	ELECTRICAL	300
Mar`24	E-H1,MHCV	7200021	CHASSIS PROP	475
Apr`24	MHCV	7200002	BAR MACHINING	322
Apr`24	MHCV	7200004	NON-METALLIC	410
Apr`24	MHCV	7200010	CASTING	475
Apr`24	MHCV	7200013	ENGINE PROP	500
Apr`24	E-H1,MHCV	7200015	FORGING	400
Apr`24	E-H1,MHCV	7200016	CASTING	336
Apr`24	MHCV	7200017	ELECTRICAL	455
Apr`24	MHCV	7200019	ELECTRICAL	300
Apr`24	E-H1,MHCV	7200021	CHASSIS PROP	475

[Prahlad]

@lbendlin @v-tangjie-msft  @amitchandak @lbendlin @ryan_mayu @Greg_Deckler @Ritaf1983  @_AAndrade

Hi Guys. Hope you're well, would be glad to get your help for the above problem.

Thanks!

[lbendlin]

I cannot assist you if you are unable to provide usable sample data. I hope someone else can help you further.