RANKX to ignore column in visual

[dats]

Hi,

I have a test dataset for which  I created a ranking like this:

RANKX(ALLSELECTED(Tabelle1[Products],Tabelle1[Category]),[SUM OF COUNT],,DESC)

I am also displaying the rank and the Products within a table and now want to include the column rating in to the visual.  I couldn't figure out how to tell the RANKX formular to not include the rating in to its calculation. Is there a way to do it with ALLEECEPT or do II need a calculate?

[Sumanth_23]

hi @dats - you can list all column want to be included in the RankX function in the highlighted section - the rank will be calculated based on the columns mentioned in the highlighted section in the below screenshot  

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[dats]

Rating is just a product rating 1,2,3 or so

[VijayP]

@dats 

Then Rating itself Ranking! I am confused. Is ranking and Rating are same or two different things and how different

[VijayP]

@dats 

Need to understand what is Rating?!

[amitchandak]

@dats ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[dats]

@amitchandak of course 🙂

Date	Products	Category	Product Rating
01.01.2020	P1	Care	1
01.01.2020	P1	Care	3,5
....	P1	Care	4

The output at the moment would be below whereas the category can be choosen through the visual filters.

Rank	Product	Rating
1	p1	1
1	P2	3,5

if i take out the rating column in my output it works as expected:

Rank	Product
1	p1
2	P2

[amitchandak]

@dats , Not very clear.

But understand the context. you have Rank on all product

rankx(all(Table[Product]),[Sum of sales])

When I view it by(Only product on row/axis) product, I will see product rank. When I add a category column (any un summarized column ) then it will display product raking inside that category.

If your Rating is un summarized or does not have aggregation then Ranking will be done for the product inside the Rating

example

City Rank = RANKX(all(Geography[City]),[Sales]) // with City Id shows Rank 1
Geography Rank = RANKX(all(Geography),[Sales]) // Work with City ID as that is a lowest level along with city
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales]) // Work with City ID 

Can you share sample output in table format?

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[dats]

@amitchandak 

Ok I see.Sorry for the confusion. I have uploaded my pbix testfile https://ufile.io/i0voo99k

The rank is the Rank cacluated by the amount a product has been displaye in example. Products are my Products and the ranking is in example average user ratings.

What I would like to acomplish is that I can display the ratings (as stars) next to the ranked products without RANKX taking the column "ranking" into account when it is calculating the rank of a product. Also I would like to choose the category filter in the visual filter panel

[v-shex-msft]

Hi @dats,

AFAIK, current power bi does not support creating a dynamic calculated column/table based on filter/slicer. Please use the measure formula instead.

BTW, can you please share this sample file to onedrive for business, I can't download the sample data from third part web driver.

Regards,

Xiaoxin sheng