Identify non partners based on 5 diferent parameters

[sanket_sk]

Hi All,

Hope you are doing well !!

I am looking for help with the tricky issue.

Please refer to sample data, I want to 5 identify non-performing partners based on 5 different parameters, (4 parameters in the Closed Sample table and one in Cancelled Table)

The logic to consider as non-performance is as follows.

• O/W Lowest is non-performer
• Commercial Call % Highest is non-performer
• No Parts % Highest is non-performer
• LWS24% lowest is a non-performer
• Cancellation% Highest is non-performer

I am looking for formula / Dax / VBA by which Power BI check each parameter, creates a virtual table or calculates at the backend to identify 5 partners which are non-performing in most of the parameters ( partner which is non-performing in all 5 parameters will come on top, then 4 ,3,2,1 like that worst partner will be on top).

Could you please help develop the formula?

Find Below both the tables and expected output graph....

Closed Sample.xlsx
	A	B	C	D	E
1	Name	O/W	Commercial Call %	No Parts %	LWS24%
2	SHIVJYOTI EERING	27	96%	44%	23%
3	SANDIPKUMAR ICES	47	42%	64%	46%
4	SAI TED	81	100%	29%	81%
5	KRISH KUMAR	70	54%	58%	69%
6	MECHA ICALS	65	86%	29%	26%
7	SHREYA UBHAI BAROT	88	21%	88%	94%
8	BALA ATORS	64	44%	42%	66%
9	POWER T LTD	45	52%	36%	95%
10	SPIRITUAL RICALS	56	96%	63%	81%
11	SATMANGLA GERATION	33	49%	96%	84%
12	YADAV ORS	56	55%	55%	50%
13	HITACHI PVT LTD	71	99%	21%	97%
14	HARIKRUPA GINEERS	85	37%	69%	61%
15	G THLAL	33	35%	38%	61%
16	KFT TRICAL	58	97%	30%	35%
17	PATEL PVT LTD	71	42%	38%	52%
18	BRAHMA EERS	36	70%	75%	72%
19	KETANKUMAR CTRIC	83	35%	85%	35%
20	KHODIYAR S	39	43%	38%	39%
21	PATEL CALS	53	34%	59%	34%
22	MESCO DISPLAY INC	43	54%	81%	65%
23	UEM ICALS CO	59	46%	61%	90%
24	UMIYA RICAL	90	76%	48%	87%
25	RADHE OHARLAL	27	43%	33%	85%
26	HITACHI ICALS	47	53%	23%	21%
27	ALKA ECTRICALS	39	32%	93%	58%
28	UHLEDISPLAY G PVT LTD	72	53%	49%	63%
29	STEIGEN PRAJAPATI	50	30%	30%	70%
30	OVERHEAD ICES	57	91%	63%	65%
31	UNIQUE ECTRICALS	73	34%	46%	71%
32	CHAMUNDA PATADIA	35	88%	22%	59%
33	DHRUVI OMPANY	40	26%	28%	33%
34	JAY ICALS	49	79%	84%	77%
35	LUCKY CHAUDHARY	39	52%	87%	88%
36	GOPI ICALS	31	23%	80%	29%
37	UNIMEEC VATORS	76	54%	66%	23%
38	MILANKUMAR ENT	58	90%	46%	26%
39	PATEL ATSINH	68	48%	25%	22%
40	HASMUKH RICALS	68	29%	82%	97%
41	GOPI CALS	67	93%	50%	73%
42	LALJANI ABLES	36	42%	52%	89%
43	ISHVARBHAI ARDAS	26	73%	52%	82%
44	RATHOD AR H BAROT	51	48%	43%	23%
45	AMISH YSTEMS	72	75%	100%	29%
46	DODIYA ALS	52	21%	82%	25%
47	HARIOM N PVT LTD	52	48%	42%	76%
48	LAKSH EVATORS	73	46%	94%	94%
49	MAAN VICES	70	97%	26%	53%
50	PRAVINKUMAR CTRICALS	34	60%	41%	77%
51	BALAJI ECTRICALS	54	33%	66%	41%
Sheet1

Cancel Sample.xlsx
	A	B
1	Name	Cancellation%
2	ALKA ECTRICALS	58%
3	AMISH YSTEMS	29%
4	BALA ATORS	88%
5	BALAJI ECTRICALS	20%
6	BRAHMA EERS	73%
7	CHAMUNDA PATADIA	47%
8	DHRUVI OMPANY	46%
9	DODIYA ALS	22%
10	G THLAL	75%
11	GOPI ICALS	40%
12	HARIKRUPA GINEERS	77%
13	HARIOM N PVT LTD	22%
14	HASMUKH RICALS	34%
15	HITACHI ICALS	58%
16	HITACHI PVT LTD	78%
17	ISHVARBHAI ARDAS	31%
18	JAY ICALS	46%
19	KETANKUMAR CTRIC	70%
20	KFT TRICAL	73%
21	KHODIYAR S	70%
22	LAKSH EVATORS	22%
23	LALJANI ABLES	32%
24	LUCKY CHAUDHARY	45%
25	MAAN VICES	21%
26	MECHA ICALS	95%
27	MESCO DISPLAY INC	69%
28	MILANKUMAR ENT	39%
29	OVERHEAD ICES	48%
30	PATEL ATSINH	36%
31	PATEL CALS	69%
32	PATEL PVT LTD	73%
33	PRAVINKUMAR CTRICALS	21%
34	RADHE OHARLAL	61%
35	RATHOD AR H BAROT	29%
36	SAI TED	99%
37	SANDIPKUMAR ICES	99%
38	SATMANGLA GERATION	82%
39	SHIVJYOTI EERING	100%
40	SHREYA UBHAI BAROT	95%
41	SPIRITUAL RICALS	82%
42	STEIGEN PRAJAPATI	49%
43	UEM ICALS CO	66%
44	UHLEDISPLAY G PVT LTD	52%
45	UMIYA RICAL	63%
46	UNIMEEC VATORS	39%
47	UNIQUE ECTRICALS	48%
48	YADAV ORS	81%
Sheet1

Thanks & Regards,

Sanket