Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Prahlad
Frequent Visitor

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

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: 

Prahlad_0-1716920710614.png

 

Here is the base data:

MonthSegmentSUPPLIER CODEDOMAINOverall Score (Rounded Off)
Nov`23MHCV7200980NON-METALLIC450
Nov`23MHCV7204096NON-METALLIC340
Nov`23MHCV7203771CHASSIS PROP425
Nov`23LCV7205672CHASSIS PROP383
Nov`23MHCV7200370CHASSIS PROP300
Nov`23MHCV7205371CHASSIS PROP340
Dec`23MHCV7200002BAR MACHINING375
Dec`23MHCV7200004NON-METALLIC407
Dec`23MHCV7200013ENGINE PROP450
Dec`23E-H1,MHCV7200015FORGING375
Dec`23E-H1,MHCV7200016CASTING275
Dec`23MHCV7200017ELECTRICAL425
Jan`24MHCV7200002BAR MACHINING343
Jan`24MHCV7200004NON-METALLIC408
Jan`24MHCV7200013ENGINE PROP500
Jan`24E-H1,MHCV7200015FORGING350
Jan`24E-H1,MHCV7200016CASTING347
Jan`24MHCV7200017ELECTRICAL412
Jan`24MHCV7200019ELECTRICAL275
Feb`24MHCV7200002BAR MACHINING369
Feb`24MHCV7200004NON-METALLIC409
Feb`24MHCV7200013ENGINE PROP500
Feb`24E-H1,MHCV7200015FORGING375
Feb`24E-H1,MHCV7200016CASTING337
Feb`24MHCV7200017ELECTRICAL455
Feb`24MHCV7200019ELECTRICAL300
Feb`24E-H1,MHCV7200021CHASSIS PROP475
Mar`24MHCV7200946RM375
Mar`24MHCV7200004NON-METALLIC409
Mar`24MHCV7200013ENGINE PROP500
Mar`24E-H1,MHCV7200015FORGING375
Mar`24E-H1,MHCV7200016CASTING337
Mar`24MHCV7200017ELECTRICAL455
Mar`24MHCV7200019ELECTRICAL300
Mar`24E-H1,MHCV7200021CHASSIS PROP475
Apr`24MHCV7200002BAR MACHINING322
Apr`24MHCV7200004NON-METALLIC410
Apr`24MHCV7200010CASTING475
Apr`24MHCV7200013ENGINE PROP500
Apr`24E-H1,MHCV7200015FORGING400
Apr`24E-H1,MHCV7200016CASTING336
Apr`24MHCV7200017ELECTRICAL455
Apr`24MHCV7200019ELECTRICAL300
Apr`24E-H1,MHCV7200021CHASSIS PROP475
Apr`24E-H1,LCV,MHCV7200023NON-METALLIC360

 

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

 

5 REPLIES 5
lbendlin
Super User
Super User

Your sample data is insufficient to determine reliable statistics.

 

lbendlin_0-1717026574053.png

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

 

v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1716951711865.png

 

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. 

@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)

Prahlad_0-1717130149732.png

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:

Prahlad_1-1717131097075.png

 

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:

Prahlad_2-1717131193665.png

 

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 )

MonthSegmentSUPPLIER CODEDOMAINOverall Score (Rounded Off)
Nov`23MHCV7200004NON-METALLIC407
Nov`23MHCV7200002BAR MACHINING375
Nov`23E-H1,MHCV7200015FORGING375
Nov`23MHCV7200019ELECTRICAL300
Nov`23MHCV7200017ELECTRICAL425
Nov`23E-H1,MHCV7200021CHASSIS PROP425
Nov`23E-H1,MHCV7200016CASTING350
Dec`23MHCV7200002BAR MACHINING375
Dec`23MHCV7200004NON-METALLIC407
Dec`23MHCV7200013ENGINE PROP450
Dec`23E-H1,MHCV7200015FORGING375
Dec`23E-H1,MHCV7200016CASTING275
Dec`23MHCV7200017ELECTRICAL425
Dec`23MHCV7200019ELECTRICAL300
Dec`23E-H1,MHCV7200021CHASSIS PROP425
Jan`24MHCV7200002BAR MACHINING343
Jan`24MHCV7200004NON-METALLIC408
Jan`24MHCV7200013ENGINE PROP500
Jan`24E-H1,MHCV7200015FORGING350
Jan`24E-H1,MHCV7200016CASTING347
Jan`24MHCV7200017ELECTRICAL412
Jan`24MHCV7200019ELECTRICAL275
Jan`24E-H1,MHCV7200021CHASSIS PROP471
Feb`24MHCV7200002BAR MACHINING369
Feb`24MHCV7200004NON-METALLIC409
Feb`24MHCV7200013ENGINE PROP500
Feb`24E-H1,MHCV7200015FORGING375
Feb`24E-H1,MHCV7200016CASTING337
Feb`24MHCV7200017ELECTRICAL455
Feb`24MHCV7200019ELECTRICAL300
Feb`24E-H1,MHCV7200021CHASSIS PROP475
Mar`24MHCV7200004NON-METALLIC409
Mar`24MHCV7200013ENGINE PROP500
Mar`24E-H1,MHCV7200015FORGING375
Mar`24E-H1,MHCV7200016CASTING337
Mar`24MHCV7200017ELECTRICAL455
Mar`24MHCV7200019ELECTRICAL300
Mar`24E-H1,MHCV7200021CHASSIS PROP475
Apr`24MHCV7200002BAR MACHINING322
Apr`24MHCV7200004NON-METALLIC410
Apr`24MHCV7200010CASTING475
Apr`24MHCV7200013ENGINE PROP500
Apr`24E-H1,MHCV7200015FORGING400
Apr`24E-H1,MHCV7200016CASTING336
Apr`24MHCV7200017ELECTRICAL455
Apr`24MHCV7200019ELECTRICAL300
Apr`24E-H1,MHCV7200021CHASSIS PROP475

@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!

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.