DAX - Divide a column over itself with different filters to get percentages

Question

In power Pivot I have tables along the lines of:

Table 1

Year	Month	Branch_ID	Store_ID	Article	Value
2022	10	1	1	Sales	100
2022	10	1	2	Sales	200
2022	10	1	2	Operating expenses	50
2022	10	1	1	Operating expenses	80
2022	10	1	2	Cost of Sales	20
2022	10	1	1	Cost of Sales	30

Table 2

Year	Month	Branch_ID	Store_ID	Article	Value
2022	10	1	1	Sales_Ecomm	20
2022	10	1	2	Sales_Ecomm	15

Table 3

| Article            |
|--------------------|
| Sales              |
| Operating expenses |
| Cost of Sales      |
| Sales_Ecomm        |

There are multiple branches and months, so these columns may not be ignored.

Table 1 and table 2 are separate. Table 3 is connected to both so that I could build a pivot table.

In the pivot table I want to have all articles re-evaluated as percentage of Sales, i.e. I am trying to get a pivot table along the lines of:

Store ID	Sales		Operating expenses		Cost of Sales		Sales_Ecomm
	Value	% of sales	Value	% of sales	Value	% of sales	Value	% of sales
1	100	100.00%	80	80.00%	30	30.00%	20	20.00%
2	200	100.00%	50	25.00%	20	10.00%	15	7.50%

I have a measure

Val. := sum(table1[Value]) + sum(table2[value])

which seems to be working for absolute values of the articles.

However, I can't seem to come up with an appropriate DAX measure for percentages. I have tried:

%_of_Sales := [Val.] / calculate([Val.], filter(table3; table3[Article]="Sales"))

but it only counts Sales as percentage of Sales (100%), yielding #NUM! for other articles in the pivot table.

How do I define a ratio measure so that every article is evaluated against Sales?

Answer 1

You're missing a crucial ALL:

=
DIVIDE(
    [Val.],
    CALCULATE(
        [Val.],
        FILTER(
            ALL( table3 ),
            table3[Article] = "Sales"
        )
    )
)

which is equivalent to:

=
DIVIDE(
    [Val.],
    CALCULATE(
        [Val.],
        table3[Article] = "Sales"
    )
)