DAX FILTERING FUNCTIONS

[BodganST]

Hi guys,

So i have this situation where i have a data model in Excel in which i would like to obtain Weight % with a DAX formula.

I've attached an example below. I want to achieve the second output ( Weight with Filter Context ) meaning that the DAX formula should act like a Show Values as % of Parent Row Total.

Country City Sales Weight w/o Filter Context Weight w Filter Context
Germany Munchen 50 11.6% 33%
Germany Berlin 100 23.3% 67%
France Paris 100 23.3% 36%
France Lyon 180 41.9% 64%

Can you tell me please how can i achieve this, without using the Show Values as % of Parent Row Total option from Pivot Table in Excel and instead create a DAX Formula ?

Thank you very much,
Best regards,
Bogdan

 

[citizenbh]

Try this:

Weight w/o Filter Context =DIVIDE([Sales], CALCULATE(SUM([Sales]),ALL(Table1)))

Weight w Filter Context =Table1[Sales]/CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[State]))

 

[BodganST]

Try this:

Weight w/o Filter Context =DIVIDE([Sales], CALCULATE(SUM([Sales]),ALL(Table1)))

Weight w Filter Context =Table1[Sales]/CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[State]))

Hi citizenbh and thank you for your reply. Unfortunately it doesn't work . To be more specific. In my DB i have Channels ( ex: Hypermarkets , Supermarkets etc . ), Categories ( Hair Care , Hair Color , Deo ) , Subcategories , Brands etc.

I need to see my Weight % regardless of the combination of dimensions that i am using that takes into account filter context..meaning let's say for example , i want to see the weight of my Brands in Hair Care Category in Hypermarkets.

Thank you,
Br

 

[citizenbh]

In this case you are arranging columns in the ALLEXCEPT function. Take care of the order and depth of the query.
For example :

=Table1[Sales]/CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[State],Table1[City] . . .))
or
=Table1[Sales]/CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Channels],Table1[Categories]))