Need Bottom 3 rows data

[SAPpowerbi]

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-

Category  Name	Value (Calculated measure)
A	10
B	20
c	30
D	40
E	0
F	0
G	0
H	200
I	100

Output Result -

we need Bottom 3 Rows data only Like -

Category Name	Value
I	100
H	200
G	0

we need only last 3 rows data not more then that .

Thank you in Advance .

[Anonymous · Accepted solution]

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]

Thank you , its working for me .

[Tahreem24]

@SAPpowerbi  You need to try TOPN solution from filter pane. I have attached SS for your reference kindly follow and apply the same filter in TOPN of category name so you'll get the required output as per your need.

[Jihwan_Kim]

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

[SAPpowerbi]

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]

Hi,

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

[SAPpowerbi]

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.