Forum Discussion

SAPpowerbi's avatar
SAPpowerbi
Helper II
4 years ago
Solved

Need Bottom 3 rows data

Hello Team ,   I need small help to write dax or any soltion for below issue. - I have requirment were i need to find bottom3 rows . below is Dummy Example for your referance - Source data- ...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi SAPpowerbi ,

     

    Whether your Value filed is a measure or a calculated column, you need to be clear about this. There is still a difference between the two.

     

    Can you enter the Power Query Editor? If you can, you can create a index in it.

     

    Then use TOP N to filter.

     

    Best Regards,

    Stephen Tao

     

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

     

SAPpowerbi's avatar
SAPpowerbi
Helper II
4 years ago

Hi
Thank you for reply ,

i got only one bottom row when i used your provided DAX .

 

Data value field is calculated column in my scenirio.

 

I am using below formula as suggested by you 

Bottom 3 rows: =
VAR bottom3table =
TOPN ( 3, ALL ( 'data' ), CALCULATE ( MAX ( 'data'[categoryName]) ), DESC )
RETURN
CALCULATE ( [Margin] ), KEEPFILTERS ( bottom3table ) )

 

Note - [Margin ]- is calculated Measure in my scenirio

 

Please help me to solve this issue .

Thank you .

 

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Could you please share your sample pbix file's link (One drive link or any other link) ?

  • SAPpowerbi's avatar
    SAPpowerbi
    Helper II
    4 years ago

    Hi ,

    Thank you for instant reply .

    Sorry i am not abel to share my PBIX file becose of security reson .

    Note- but when i put your dax in to my file , it is working but only for 1 row like last row only , i need Bottom  3 rows .
     
    Can you please expalin me how to calculate max category name in this dax , becose i got only 1 row as max not 3 rows .

    Below is your dax -
    Bottom 3 rows: =
    VAR bottom3table =
    TOPN ( 3, ALL ( Data ), CALCULATE ( MAX ( Data[Category Name] ) ), DESC )
    RETURN
    CALCULATE ( SUM ( Data[Value] ), KEEPFILTERS ( bottom3table ) )

    Thakn you , Please help me to solve this issue , i think we are near to the output.