bi-passing filter selection

[kanth123]

Hi ,

I have data as representated below. I have Page level filters on Year,Category and Subcatagory.

I want to bi-pass "Category" and "Subcatgory" filter selection on Tagert sales.

Any idea how this can be acheived.

Product|Year|Category|Subcatagory|Amount|TargetSales
Headphones|2020|Electronics|Speakers|100|0
Speakers|2019|Electronics|Speakers|200|0
Airpods|2020|Electronics|Speakers|300|0
Outdoorspeakers|2020|Electronics|Speakers|200|0
Handblenders|2019|HomeAppliances|Kitchen|100|0
Mixers|2020|HomeAppliances|Kitchen|100|
Headphones|2020| - | - | 0 | 300
Speakers|2019| - | - | 0 | 500
Airpods|2020| - | - | 0 | 200
Outdoorspeakers|2020| - | - | 0 |400
Handblenders|2019|-|-|0|200
Mixers|2020|-|-|0|400

Thansk in Advance..

[Anonymous]

Hi @kanth123 ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @kanth123 ,

Not very clear...Do you want to ingore the page level filter of Year?

You could use ALLEXCEPT() function like this:

Measure =
CALCULATE (
    SUM ( 'Table'[TargetSales] ),
    ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Subcategory] )
)

Here is the pbix file.

Best Regards,
Eyelyn Qin

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

[kanth123]

Hi @Anonymous 

Sorry for delayed response..

I tried to implement AllExpect in measure calculation, When subcategory or Category  are selected targetsales becoming 0 in table.

Thanks.

[Anonymous]

Hi @kanth123 ,

0 is not surprising because there are some 0 values in original data.

As shown before, I selected both subcategory and category,everything seemed worked.

Could you please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business?

Best Regards,
Eyelyn Qin

[kanth123]

@amitchandak Thanks for your response, I can't use stop intraction functionality. In the report I am showing Product, Sum (Amount), Sum (TargetSales). so when the user selects Year, they must apply a filter on both "Amount" and "Target sales". Category and subcategory selections should not affect Sum (Target Sales)

Thank you..

[amitchandak]

@kanth123 , One way is to stop interactions. - https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

The second is to have a category table with category and subcategory and use all on that

calculate(countrows(Table) , all(Category [Category ]))

or use on the same table

calculate(countrows(Table) , all(Table[Category],Table[Subcatagory]))