Top N dimension values based on the selected slicer value

[kumsha1]

Hi,

I have a requirement to display previous 30 values of the dimension based on the slicer selection and corresponding measures in a bar chart. From below example data, if the user slectes MI040 in slicer then bar chart should display values from MI010 to MI040 along with corresponding measure values. Can someone please help with this?, Many thanks !..Cheers

ShipmentNo M1 M2

MI001           10  10

MI002           12  14

MI003           30   34

MI004           40  44

------

------

MI040         200  220           

[v-deddai1-msft · Accepted solution]

Hi @kumsha1 ,

You can create a custom column in query editor to get number value in your ShipmentNo column:

Table.TransformColumnTypes(Table.AddColumn(#"Renamed Columns", "No", each Text.Select([ShipmentNo], {"0".."9"})),{{"No", Int64.Type}})

Then use the following measure in your bar chart.

Measure =  IF(MAX('Table'[No])<=SELECTEDVALUE('Table'[No]) && MAX('Table'[No])>=SELECTEDVALUE('Table'[No])-30,CALCULATE([Your Measure],ALL('Table')),Blank())

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

Best Regards,

Dedmon Dai

[kumsha1]

@v-deddai1-msft 

Thanks for your reply, i was trying in the same direction and created a column for Shipment Number # but the measure is not changing the graph X-axis values?. Ex: if the user selected 40 in the report slicer then bar chart X-axis should only display values from 10....40 and corresponding measure values, i.e. Previous 30 Shipment Numbers # based on the Slicer selection...

[v-deddai1-msft]

Hi @kumsha1 ,

You'd better create a new table with Shipment Number column and use it as slicer .

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

Best Regards,

Dedmon Dai

[amitchandak]

@kumsha1 , You have create a Rank on shipment no

Column Rank

Ship rank =rank(All(Table),Table[[ShipmentNo],asc,dense )

Meausure =
var _max =maxx(allselected(Table),Table[rank])

return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(Table),Table[rank]<=_max && Table[rank]>=_min ))

but you might need to have an independent table to display shipment no

Like this example :https://www.youtube.com/watch?v=duMSovyosXE