# DAX FILTERING FUNCTIONS

#### BodganST

##### New Member
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

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### citizenbh

##### Board Regular
Try this:

Code:
``````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

##### New Member
Try this:

Code:
``````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

##### Board Regular
In this case you are arranging columns in the ALLEXCEPT function. Take care of the order and depth of the query.
For example :
VBA Code:
``````=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]))``````

Replies
6
Views
790
Replies
11
Views
630
Replies
1
Views
234
Replies
1
Views
222
Replies
1
Views
233

1,109,406
Messages
5,528,591
Members
409,827
Latest member
Tmcgrew05

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...