Forum Discussion

Mic's avatar
Mic
Frequent Visitor
4 years ago

Rank Second Largest Criteria - Calculating Values

Dear Community,

i'm pretty fresh and new with Power Bi and already now confronted with a task i'm very much struggeling with.

already spend multiple hours analyzing this forum but did not find the fitting solution - hope s.b. in here will be able to guide me / sharing a solution.

 

Let me introducte to you the topic:

I do have a file containing 120k+ line items with ~40 columns.

Most important is, table is created by appending multiple single files from a folder, each of single file contains a column called "extract_date".

Format of this column is like: yyyymmdd, recognized as "number"

 

Looking into that column, now i need to get out for latest extract (my idea - highest number = formula 'max') the total of a column called "CashOut".

by using this formula (see below) i was already able to get it for latest data extract:

 

 

Total CashOut ACT = 
VAR vMax =
    MAX('01_Liqui_GP_View_fin'[Extract_Date]
    )
Return
    CALCULATE(
       SUM('01_Liqui_GP_View_fin'[FX EUR Due]), Filter('01_Liqui_GP_View_fin', vMax)
       )

 

 

 

Now it is about the same calculation but for second highest value - issue with this: i'm currently struggling to find a method getting this "secong out...

 

Anybody who might be able to help me solving this?

 

bR in advance

Mic

Need Help
Show and Tell

4 Replies

    • Mic's avatar
      Mic
      Frequent Visitor
      4 years ago

      Hi Nickolaj,

       

      first many thanks for your reply - i will check this on short note and will let you know if i was able to bring this into my perspective.

       

      bR Mic

  • NickolajJessen's avatar
    NickolajJessen
    Icon for Solution Sage rankSolution Sage
    4 years ago

    and i just realized that's not what you asked for, haha.

    Here is for the second highest value

     

     

  • Mic's avatar
    Mic
    Frequent Visitor
    4 years ago

    Hi Nickolaj,

    back again and with feedback - unfortunately i was not able to get this transfered to my file, as i have some issues with the format of data - means additional "format changes" failed, due to the way i receive them.

     

    BUT - while digesting on that i found out a function, i was not aware of before, which is the "index column" funtion. 

    While processing the combination of data and transformation, i entered an index in first steps to define oldest dataset with index "0" and newest with 3 - so previous dataset will be defined by index 2.

     

    Then calculation seems to work - this is only in review now.

     

    Nevertheless, many thanks for your feedback and support.

     

    best 

    Mic