How to prevent Grand total in the table from getting filtered by slicer?

[PuR]

Hi, I've a fruit table with slicer that filters fruit type. It has built-in total function showing the total fruits for each person.How do I make Total (fruits) remain unchanged even when the slicer is being selected for different fruit type? Is there a way to do it? Any help would be appreciated. Thank you.

[PuR]

Thank you, @amitchandak and @parry2k, for your help. I checked the video linked by parry2k but it didn't seem to work out for me. Maybe I am not understanding it correctly. Or maybe I wasn't able to explain what I meant to ask in my question. I found a solution. It is inefficient and a bit tedious. I am learning and if anyone found a better solution, please kindly post it here for a future reference.

Here is my solution:

Two same table: Table1 and Table2.Minimizing Table2, and only showing grand total.Date table is only connected to Tabel2, unlike Table1.Selecting Month slicer.Selecting Month with two fruit types.Final picture.

Thank you once again, amitchandak and parry2k, for your help.

[parry2k]

@PuR great solution from @amitchandak , check my video on ISINSCOPE and ISFILTEREDand see if it helps you to learn further. https://youtu.be/Nd1jP9C8ZvQ

✨ Follow us on LinkedIn and  to our YouTube channel

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!

[amitchandak]

@PuR . Create a meausre like

if(isinscope(Table[fruit]), [Measure], calculate([measure], all()) )

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[PuR]

Hi amitchandak, thank you for your reply. I am new to dax measure so could you go step further and explain the "Measure" and "calculate[measure]" in the dax formula? I checked the link you attached and I am still lost. Why or how to create isTotal column because Seller has Total country in its row? Is "Measure" == Measure and "measure" == Measure 2 in the kasper on bi example? Thank you.

[amitchandak]

@PuR , Please refer to the video from @parry2k , which should help how isinscope and isfiltered work.

Assume you have a measure that is used in your visual. You need to use the same other than grand total. So if the check is checking if fruit type isinscope , if that is true use measure. else you can use a measure with all filter removed calculate([Measure] ,all())

if(isinscope(Table[fruit Type]), [Measure], calculate([measure], all()) )