Rankx with two subcategories

[rob7one]

Hello guys,

can someone tell what is wrong here?

Rankx with 2 categories = CALCULATE(RANKX(ALL(a_notifi_list,a_notifi_list[Name 1 /vendor name]),a_notifi_list[Defective (external)],,DESC),a_notifi_list[format date_yyyy.mm],a_notifi_list[Plant for material /plant number])

It displays the following error:

Multiple table arguments are not allowed in the ALL/ALLNOBLANKROW/REMOVEFILTERS function.

[rob7one]

Now, i would like to rank all the suppliers by amount of defects for every month and plant (in a common measure or column).

something like this:

The final result is to know often each supplier was in top five ranking (in the picture is top3) in the last 12 months and how often is every factory affected by this.

final report visual is this:

y-axis : vendors listed

x-axis amount of being present in top five ranking last 12 months (in every month)

legend: factory A, factory B, and so on

I hope it's clear a bit more now.

[rob7one]

I try to describe my case a bit:

I would like to rank an amount of defects within 3 columns: month, plant and vendor column.

That means to show the rank by defects for all vendors for each plant and each month. Can't find a working solution for this for a long time. Is it possible to do it in a custom column? Because i need to count all the vendors with rank 1-5.

My idea is to create an additional column and set for every rank 1-5 the value "1" and for all the other ranks the value "0" and then just sum all the "1" values for every vendor. Just to know how often a vendor was in the top 5 ranking in every plant for every month.

[v-zhangti]

Hi, @rob7one 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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

[rob7one]

Hello Charlotte 🙂

here is a table with sample data:

mm.yyyy	factory	supplier	amount of defects
01.2022	B	xx	12
01.2022	C	cc	23
03.2022	D	yx	34
01.2022	A	yc	45
03.2022	B	xy	56
01.2022	D	cx	1
03.2022	A	xx	0
01.2022	B	cc	8
01.2022	D	cx	300
01.2022	A	xy	23
01.2022	B	cy	65
02.2022	C	er	87
02.2022	D	tr	11
01.2022	C	xc	65
01.2022	D	cx	300
01.2022	A	xy	23
01.2022	B	cy	65
02.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
03.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
02.2022	E	qw	101
02.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
02.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
02.2022	C	tz	87
02.2022	D	kl	67
03.2022	E	qw	101
03.2022	C	tz	87
03.2022	D	kl	67
02.2022	E	qw	101
03.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
02.2022	C	tz	87
03.2022	D	kl	67
02.2022	E	qw	101
01.2022	A	yy	1
01.2022	B	xx	12
01.2022	C	cc	23
03.2022	D	yx	34
01.2022	A	yc	45
03.2022	B	xy	56
01.2022	C	cy	56
01.2022	D	xc	45
01.2022	A	cx	34
01.2022	B	yx	23
01.2022	C	cx	12
01.2022	D	cx	1
03.2022	A	xx	0
01.2022	B	cc	8
01.2022	C	xc	65
01.2022	D	cx	300
01.2022	A	xy	23
04.2022	B	cy	65
02.2022	C	er	87
02.2022	D	tr	11
04.2022	C	xc	65
01.2022	D	cx	300
01.2022	A	xy	23
01.2022	B	cy	65
02.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
03.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
02.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
02.2022	C	tz	87
04.2022	D	kl	67
02.2022	E	qw	101
02.2022	D	kl	67
02.2022	E	qw	101
02.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
02.2022	C	tz	87
02.2022	D	kl	67
04.2022	E	qw	101
02.2022	C	tz	87
02.2022	C	tz	87
02.2022	D	kl	67
02.2022	E	qw	101
03.2022	E	qw	101
03.2022	C	tz	87
03.2022	D	kl	67
03.2022	C	tz	87
04.2022	C	tz	87
04.2022	D	kl	67
04.2022	E	qw	101

[smpa01 · Accepted solution]

@rob7one A measure to capture supplier Rank by mm.yyyy and factory

Measure = 
RANKX (
    FILTER (
        ALL ( 'Table 1' ),
        'Table 1'[mm.yyyy] = MAX ( 'Table 1'[mm.yyyy] )
            && 'Table 1'[factory] = MAX ( 'Table 1'[factory] )
    ),
    CALCULATE ( MAX ( 'Table 1'[amount of defects] ) ),
    ,
    DESC,
    DENSE
)

PFA Workbook

[rob7one]

That could really help! i will check it tomorrow to verify my results! Thanks a lot my friend!

i use this rankx measure for an other visual:

A_rank top 5 history 2 = RANKX(ALL(a_notifi_list[vendor name]), CALCULATE(SUM(a_notifi_list[Defectives)])))

it seems to work, but when i use it in a matrix like you can see below, it shows me for each vendor the value "1". i think that could be vendors which are not present with defects in the column of the month, but i'm not sure. Do you know how i can fix this? on the left side of the matrix are all vendors listed row by row.

[jaweher899]

please try 

Rankx with 2 categories =
CALCULATE(
RANKX(
CALCULATETABLE(
ALL(a_notifi_list),
a_notifi_list[format date_yyyy.mm] = SELECTEDVALUE(a_notifi_list[format date_yyyy.mm]),
a_notifi_list[Plant for material /plant number] = SELECTEDVALUE(a_notifi_list[Plant for material /plant number])
),
a_notifi_list[Defective (external)],
,DESC
),
ALL(a_notifi_list)
)

[rob7one]

Hi @jaweher899 

it shows me the following error:

A single value for column 'Defective (external)' in table 'a_notifi_list' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

How i can fix this?

Thank you in advance !