CALCULATE FILTER CONTEXT

[Anonymous]

I'm trying to create two measures and these are the formulas in human language that I'm using:

- Share corp = sales filtered/sales company
- Share div = sales dept/sales div

I have three filters:

Dir
Div
Dept

These are the tables:

SalesTable
sales	dir	div	dept
99	A	Div1	dept1
21	A	Div1	dept2
74	A	Div2	dept3
48	A	Div2	dept3
54	B	Div3	dept4
18	B	Div4	dept5
1	C	Div5	dept6
65	C	Div5	dept6
16	C	Div5	dept7
30	C	Div5	dept7

StructureTable
dir	div	dept
A	div1	dept1
A	div1	dept2
A	div2	dept3
B	div3	dept4
B	div4	dept5
C	div5	dept6
C	div5	dept7

Is there a way to get "share div" WITHOUT having to filter a dept_nbr in the slicer before? Because now, to get that, the only way I figured to make it work is by first filtering a div, and then filter a dept. If I filter the dept only, without filtering the division first, I get the "share corp" result instead of the "share div" one.

In short words, I need DAX to understand that when I select a dept, he has to divide total sales of that dept by total sales of the div that this dept belongs to, without having to filter the div in the silcer before.

Thank you all in advance.

[wdx223_Daniel]

does this work?

Share Div = DIVIDE(SUM(SalesTable[sales]),CALCULATE(SUM('SalesTable'[sales]),ALL('StructureTable'[dept])))

[lbendlin]

Please explain why you repeat the structure in the Sales table.  The Sales table should only contain the dept and sales columns.

[Anonymous]

Hi @lbendlin, thank you for your time.

That's a good point. If we modify it, the measure I'm asking for, would be possible to ahieve?

Thanks again.