Ignore slicer specifics but not general filter

[PowerBIWhat]

Hi,

I have a dataset that is laid out like the below:

StartDate

EndDate

Region

Product

ProductType

Sales

SalesRank

Profit

ProfitRank

Every product is given a ranking based on sales/profit when comparing this to the other products in the same region and that are the same product type.

In PowerBi the user has the ability to filter on the date and then by Region and Product which will then show them the specific ranking for that product, alongside the metrics which make up the ranking.

What I want to be able to do in a separate table is show comparable Products. E.g. A Product which is ranked at the top and one which is ranked below. The table needs to ignore the Product filter but still filter on region/product type based on the Product filter.

Is this possible?

Thanks,

O

[amitchandak]

@PowerBIWhat , Not very clear. But you can create top and bottom rank and filter those

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

example

City Rank = RANKX(all(Geography[City]),[Sales])

City Rank 1= RANKX(all(Geography[City]),[Sales],asc,dense)

Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10 || [City Rank1] <=10,[Sales],BLANK()) )

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...

[PowerBIWhat]

Hi,

It is the filters i am having a problem, the ranking i have done in SQL in the source dataset. I have a sample .pbix but not sure how to share on here.

Thanks

[v-xulin-mstf]

Hi @PowerBIWhat,

You can upload the pbix file to a network drive and then share the public link.

Best Regards,

Link

[PowerBIWhat]

@v-xulin-mstf  @amitchandak 

https://drive.google.com/file/d/1BCijiGuTOvwxmPRWn166RB72QgwdRzsR/view?usp=sharing

[v-xulin-mstf]

Hi @PowerBIWhat

Try measure as:

Measure = 
IF(
    MAX(Sheet1[ProductType])="Electronics",
    1,
    0
)

 The pbix file is attached.

Best Regards,

Link

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

[PowerBIWhat]

Hi,

That doesn't seem to be dynamic, I want the table to change to show product type 'Cleaning' if the product selected is Oven. Essentially, I want that table to show the comparators of the product I have selected, dynamically based on the filtered product.