Forum Discussion
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-
| 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 .
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.
7 Replies
Bottom 3 rows: =
VAR bottom3table =
TOPN ( 3, ALL ( Data ), CALCULATE ( MAX ( Data[Category Name] ) ), DESC )
RETURN
CALCULATE ( SUM ( Data[Value] ), KEEPFILTERS ( bottom3table ) )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 .
Hi,
Could you please share your sample pbix file's link (One drive link or any other link) ?
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.
Thank you , its working for me .
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.
