Forum Discussion

rob7one's avatar
rob7one
Icon for Helper I rankHelper I
3 years ago
Solved

Rankx with two subcategories

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

8 Replies

  • jaweher899's avatar
    jaweher899
    Icon for Impactful Individual rankImpactful Individual

    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's avatar
      rob7one
      Icon for Helper I rankHelper I

      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 !

  • 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's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support

      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's avatar
        rob7one
        Icon for Helper I rankHelper I

        Hello Charlotte 🙂

         

        here is a table with sample data:

         

        mm.yyyyfactorysupplieramount of defects
        01.2022Bxx12
        01.2022Ccc23
        03.2022Dyx34
        01.2022Ayc45
        03.2022Bxy56
        01.2022Dcx1
        03.2022Axx0
        01.2022Bcc8
        01.2022Dcx300
        01.2022Axy23
        01.2022Bcy65
        02.2022Cer87
        02.2022Dtr11
        01.2022Cxc65
        01.2022Dcx300
        01.2022Axy23
        01.2022Bcy65
        02.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        03.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        02.2022Eqw101
        02.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        02.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        02.2022C tz87
        02.2022D kl67
        03.2022Eqw101
        03.2022C tz87
        03.2022D kl67
        02.2022Eqw101
        03.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        02.2022C tz87
        03.2022D kl67
        02.2022Eqw101
        01.2022Ayy1
        01.2022Bxx12
        01.2022Ccc23
        03.2022Dyx34
        01.2022Ayc45
        03.2022Bxy56
        01.2022Ccy56
        01.2022Dxc45
        01.2022Acx34
        01.2022Byx23
        01.2022Ccx12
        01.2022Dcx1
        03.2022Axx0
        01.2022Bcc8
        01.2022Cxc65
        01.2022Dcx300
        01.2022Axy23
        04.2022Bcy65
        02.2022Cer87
        02.2022Dtr11
        04.2022Cxc65
        01.2022Dcx300
        01.2022Axy23
        01.2022Bcy65
        02.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        03.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        02.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        02.2022C tz87
        04.2022D kl67
        02.2022Eqw101
        02.2022D kl67
        02.2022Eqw101
        02.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        02.2022C tz87
        02.2022D kl67
        04.2022Eqw101
        02.2022C tz87
        02.2022C tz87
        02.2022D kl67
        02.2022Eqw101
        03.2022Eqw101
        03.2022C tz87
        03.2022D kl67
        03.2022C tz87
        04.2022C tz87
        04.2022D kl67
        04.2022Eqw101
  • 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.