RANKX - Best way to select only 1 element

[Anonymous]

Hi All,

hope you can help me to find the best way to select only one ranked element of a table and display only one scalar value on the card visual, for example. 

Examples.

++| category | product | sales amnt. | ranking by sales|++

++|      A       | shoe1     | 300             |  1                       |++

++|      A       | shoe2     | 200             |  2                       |++

++|      A       | shoe3     | 100             |  3                       |++

++|      A       | shoe4     | 50               |  4                       |++

So now, I would like to select only the sales amount. of given ranking value, so that I can create 4xCard Visuals in horizontally and insert the elements.

Example.

Card1 --> I would display the sales amount of element ranked 1

Card2 --> I would display the sales amount of element ranked 2

and so on... 1 element per Visual, preferring card visual, so working with a scalar value.

The only solution I can think is calculating one measure per ranking so that I can select the sales amount where ranking=1 or 2 or 3 and so on... to retrieve only one value and to insert it in a card visual... There is another option to do this? 

I would avoid inserting a table element with several columns that then I need to hide... I have already resolved in this way but the work is so heavy and tedious.

Thanks 🙂

[v-jayw-msft]

Hi @Anonymous ,

As far as i know, Measures are not supported to be added as a filter in Card Visual. You will need to create a measure for per ranking as below.

sum = SUM('Table'[sales amnt.])
rank = RANKX(ALL('Table'),[sum],,DESC)
[rank=1] = CALCULATE(SUM('Table'[sales amnt.]),FILTER(ALL('Table'),[rank]=1))
[rank=2] = CALCULATE(SUM('Table'[sales amnt.]),FILTER(ALL('Table'),[rank]=2))
[rank=3] = CALCULATE(SUM('Table'[sales amnt.]),FILTER(ALL('Table'),[rank]=3))
[rank=4] = CALCULATE(SUM('Table'[sales amnt.]),FILTER(ALL('Table'),[rank]=4))

However, Calculated Columns are supported to be added as a filter in Card Visual.

rankx = RANKX(ALL('Table'),'Table'[sales amnt.],,DESC)

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @v-jayw-msft,

Thanks to put in a ordered way what I've thought to works with scalar values.

About the calculate column, is it not possible to calculate a sub-table with dax and then put it into a visual like you've done?

Thanks 🙂

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

If there is an active relationship between main-table and sub-table, the answer is yes it is possible.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @v-jayw-msft,

Ok many thanks for the prompt replies!!

Have a nice day!!

[PaulDBrown]

@Anonymous 

Better than writing "n" number of measures is to use the filter pane to filter the measure itself to deliver the rank per visual:

EDIT: I've just tried with a card visual and it doesn't seem to allow for a filter by a measure, so if you decide to go down this route, you may have to get creative with the table formatting ...or use some other visual (KPI visual for example)

[Anonymous]

Many thanks for your reply, what you are saying is exactly my current solution but hiding all the columns that I don't need it is really tedious and if I want change a detail is the hell... Hope there is another solution to this.

The KPI visual is another option, but I think that selecting just one element of the ranked ones would be interesting, i leave the question opened for the moment

Thanks!!

[JarroVGIT]

You can also create a calculated column in your Sales table to include the RANK and then filter on that, rather then using a measure. Depends on how you are using the Card visuals (are they static regardless of filters in the report? or do you want them to be dynamic?) Otherwise you do indeed have to write ' n' measures to dynamically caluclate and filter on the ranking. I tested it succesfully with this:

1stRank = CALCULATE(AVERAGE(Sales[SalesAmount]), FILTER(ADDCOLUMNS(Sales, "@Rank", RANKX(Sales, Sales[SalesAmount])), [@Rank] = 1))

I use AVERAGE because the filtered context might have a tie (for example, if you have another SalesAMount of 300 in your table, you still want the returned value to be 300 and not 600 for example. When you now filter, the visuals will update dynamically based on the sliced context. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂