Show the top n value that is not affected by the slicer

[Anonymous]

I am trying to show the 1st provider in the area based on the stock_numbers in the map that is filtered by slicer to any of the providers available. For example, in slicer I select the provider "A" which owns 10 units in Leicester.

Now I want to see which provider is first in the area and how much stock they own. In my example, this would be provider B with 40 units. However, I can't make the measure to ignore the slicer, because it will still return provider "A" with 10 units.

I managed to make this happen outside of the map, where I disabled the interactions with the slicer. However, as soon as I try to include it in the map and use ALL function in the measure, it won't work as desired.

Here are the formulas that will work when the interactions with slicer are disabled:

For Top provider in the area:

topn2 = TOPN(
    1,
    VALUES(group_structure[provider_or_group_name]),
    CALCULATE(
        SUM(owned_stock[stock_numbers])),
        DESC
)

For number of units they own:

Measure = MAXX(
    SUMMARIZE(group_structure,group_structure[provider_or_group_name],
    "Amount",
    SUM(owned_stock[stock_numbers])),
    [Amount])

This is what I tried to ignore the slicer:

For Top provider in the area:

top1 = TOPN(
    1,
    VALUES(group_structure[provider_or_group_name]),
    CALCULATE(SUM(owned_stock[stock_numbers]), ALL(group_structure[provider_or_group_name])),
    DESC)

For Stock number of units they own (this I coulnd't figure out to work):

Measure 2 = MAXX(
    SUMMARIZE(group_structure,
    group_structure[provider_or_group_name],
    "Amount",
        CALCULATE(SUM(owned_stock[stock_numbers]),
        ALL(group_structure[provider_or_group_name])),
    [Amount]))

Sample data here (please note that in my case, the calcuation works with 2 tables, as some providers belong to a group):

provider	location	type_of_stock	stock_numbers
A	London	1	10
A	London	2	30
A	Manchester	1	20
A	Leicester	1	5
A	Leicester	2	5
B	London	1	20
B	London	2	5
B	Manchester	1	5
B	Leicester	1	20
B	Leicester	2	20

[V-lianl-msft]

Hi @Anonymous ,

You can try to clear the filter of the table with "ALL (table)".

If it doesn't work, Please provide sample data of the two tables involved in your measure and show the result you want with image(Please mask any sensitive data before uploading.)

Best Regards,

Liang

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