% of Filtered Total

[Anonymous]

Hi,

I am trying to get a very simple (or so I thought it was) DAX expression for the following scenario:

I have sales for multiple brands and multiple geographies. I want to show geographies and what the % of total sales are for whatever Brand is filtered for all of the geographies.

Any ideas?

Any help?

[v-jayw-msft]

Hi @Anonymous ,

You can use ALLEXCEPT() function.

Check the pbix as attached and refer to the formula see if it helps.

If you can show some sample data and expected out put then it will be great helpful.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

@v-jayw-msft 

Thank you....One thing I failed to mention is that this formula is going to be in a decomposition tree, therefore when user selects first branch it would be brand, then they select next branch which would be sub-category, then next branch segment.

How do we account for different dimension as they drill through branches of decomp tree?

[v-jayw-msft]

Hi @Anonymous ,

So basically you want sales for each brand/total sales in first branch, sales for each brand and geographies/total sales in second branch?

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[az38]

Hi @Anonymous 

It actually looks pretty easy, but also it has a few solution.

So, please give an example of data model and desired output for more appropriated one

[Anonymous]

@az38 MY BI file is 279m so not sure how to do this?

[az38]

@Anonymous 

just part of your data.

it could be dummy data, 10-15 rows to understand how exactly do you want to calculate it

I can try to write you a very common statement like

Measure = 
DIVIDE(
CALCULATE(SUM(Table[Sales]), ALLEXCEPT(Table, Table[Brand], Table[Geography])),
CALCULATE(SUM(Table[Sales]), ALLEXCEPT(Table, Table[Geography]))
)

I hope it will help you

[Anonymous]

@az38 WIll that DAX work if this is meant for a decomposition where I continue clicking down a hierarchy?

[az38]

@Anonymous 

will it work or not depends on data model. in common case - yes, it will

you can share your Excel into cloud service and give the link here

[Anonymous]

@az38 

Here it is:

https://1drv.ms/u/s!AtNzKwvfh50Pi_ACyJVCFKrr5aVr5w?e=gggdMS

[az38]

@Anonymous 

you can use option "Show value as" to get smth like this

[Anonymous]

@az38 when you put the data into a decomposition tree and filter on brands the numbers are not accurate. Try to do that on your end.

[az38]

@Anonymous 

you've got a lot of columns here. what do you want to achieve?

[Anonymous]

How do I attach the Excel dummy data?