Measure that ignores page level filters in a table

[tim10]

Hello

I have a visual like the one below

I have a page level filter for Stage. 

I want the special calculation measure to have the capability of ignoring page level filters. 

When someone excludes Execution for example, from the page level filters, the table should look like this:

Notice how the values for Net Sales are missing for the Execution lines but for  column Special Calculation they are there.

How can I achieve this? I tried multiple options with ALL, ALLSELECTED, REMOVEFILTERS, etc. for the Special Calculation Measure but none works. Not only that it still reacts to the page filter but it also sums up all available values for each line which I do not want.

Any suggestion is highly appreciated.

Thank you

[parry2k]

show items with no data will not work for sure

do have star schema or all the dimensions are in one table? If there is no start schema, it is one of the flaw in the model

[tim10]

There are 40 tables in the actual model and they are all linked properly. Currently users can pick dimensions to be used as page filters from any of the 40 tables and all work correctly. There is this new special request as mentioned in my original post. Thank you

[themistoklis]

@tim10 

It may be simpler by selecting on table 'Show items with no data'

[tim10]

@themistoklis  thank you for your answer but I do not see how this helps. The filter currently excludes the rows for both columns. I want it to only exclude it for one. 

[parry2k]

@tim10 you should have another table for the stage, without any relationship and use this on-page level filter and in your measure use TREATAS to filter your net sales measure and that will do it

Net Sales = 
CALCULATE ( SUM ( Table[NetSales] ), TREATAS ( VALUES ( NewTable[State] ), Table[Stage] ) )

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[tim10]

Thank you for your answer. The Stage is just one example. Users have the possibility of picking more than 200 fields to be used as page filters so creating tables for each is not doable.