Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rankx with Dates

  Hi all,    I have the table like that and want to create a report with a RankX     Customer Material Order Date RankX A 123 1.1.2019 1 A 123 1.1.2019 2 A 123 3.3.2019 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

     

    Create a Calculated Column

     

    RANK Column = RANKX(FILTER('Table','Table'[Customer] = EARLIER('Table'[Customer]) && 'Table'[Material]= EARLIER('Table'[Material])),'Table'[Order_date],,ASC)
     
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi all, 

 

both solutions are showing different numbers from what I expected.

Let me rephrase my request.

 

I have this table:

 

Format:

Soldto:      123/ABC

Material:    123/ABC

Order date:  Whole number (I displayed the dates in the table below in date format)

 

CustomerMaterialOrder Date
A1231.1.2019
A1232.2.2019
A1233.3.2019
A14510.1.2019
A14512.1.2019
A1867.1.2019
B12320.1.2019
B1239.1.2019

 

I want to create a report, which shows me the following.

A ranking (does need to be with formula rank) like this:

 

I want to see on (Customer & Product) level a ranking of the order dates. 

For example

Customer A with Product 123 for order date: 1.1.2019 = 1

Customer A with Product 123 for order date: 2.2.2019 = 2

Customer A with Product 123 for order date: 3.3.2019 = 3

Customer A with Product 145 for order date: 10.1.2019 = 1

Customer A with Product 145 for order date: 12.1.2019 = 2

...

 

Do you understand what I mean?

 

BR

Lanko

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

You can use the below meaures.

 

Ranking =

var _a = MAX(Table1[Customer])
var _b = MAX(Table1[Material])

return

RANKX(FILTER(ALL(Table1[Material],Table1[Customer],Table1[Order Date]), Table1[Customer] = _a && Table1[Material] = _b), CALCULATE( MAX(Table1[Order Date])),,ASC ,Skip)



 
 
 
Thanks and Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks a lot Anonymous ,

     

    I added measureB sumx (Format:numbers) to the report and now I want to add another MeasureC to the report.

     

    MeasureC

    Shall be the variance (var.p) for every "Customer and Material" combination like the rankx.

    Unfortunately var.p needs a column and don't work with measureB.

     

    Can somebody help me out here? 

     

     

    CustomerMaterialOrder Date

    MeasureA

    Rankx

    (based on your filter formula)

    MeasureB

    Sumx

    (based on your filter formula)

    MeasureC

    Var.P("on MeasureA")

    based on 

    A1231.1.2019   
    A12310.1.2019   
    B......   
    B     
    C     
    C     

     

    Thanks

    lanko

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous ,

       

      Try 

       

      Measure = VARX.P(ALL(Table3),[Ranking])
       
       
      Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    power bi is running into performance issues with your nice measure as I want to use that formula in a complexer modell.

     

    To receive a better performance, I want to work with tables instead of reports. 

    So I created a table with the formula summarize and entered the Columns (Customer, Material, Order Date)

     

    Unfortunately your measure is not working in the table. 

     

    Could you help me with that? 

     

    Many Thanks

    Lanko

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Which formula , the rankx formaula or the Var.p formula

       

      Also, please share some sample of the table you have created.

       

      Regards,

      Harsh Nathani

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous,

         

        thanks for the quick reply. I'm talking about the rankx formula. 

         

        I created the table: Report_Table

        Report_Table = SUMMARIZE(table1;table1[customer];table1[material];table1[Order_date])

         

        Ranking1(calculated column):

        Ranking1 =

        var a = MAX(Report_Table[customer])
        var b = MAX(Report_Table[Material])

        return

        RANKX(FILTER(ALL(Report_Table[Soldto_number];Report_Table[Material_number];Report_Table[Order_date]); Report_Table[customer] = a && Report_Table[customer] = b); CALCULATE(MAX(Report_Table[Order_date]));;ASC;Skip)

         

        Report_table:

        CustomerMaterialOrder_dateRanking1
        A1231.2.20191
        A1235.2.20191
        A1236.3.20191
        A1241.2.20191
        B12310.4.20191
        B12315.6.20191
        C1243.8.20191
        C12415.9.20191

         

        So basically the ranking is not working like that. 

        Do you have an idea on that? 

         

        BR

        Lanko