How to use filter with cell reference in row (CALCULATE, FITLER, Cell rereference)

[BobbyDollar]

Hi

Support for a beginner needed 

I want to calculate a sales share per region and profit center in a new column. In bold the column I want to calculate.

Customer_ID	Region	Profit_Center	Revenue	Region_Share
1	DE	PC1	100	33%
2	DE	PC1	200	67%
3	DE	PC2	100	25%
4	NL	PC2	200	100%
5	DE	PC2	300	75%

For line 1 and 2 I could calculate as follows:

Region_Share=Table[Revenue] / CALCULATE(SUM(Table[Revenue]);FILTER(Table;Table[Profit_Center]="PC1"); FILTER(Table;Table[Region]="DE"))

But this would not work for the complete database due to the fixed strings. I do not want to fix the filter by a text string like "PC1" and "DE". I want a reference to the cell in this row to use the Profit Center and Region information for calculation.

Any idea how to do this?

[Zubair_Muhammad · Accepted solution]

@BobbyDollar 

Try following

Column =
[Revenue]
    / CALCULATE (
        SUM ( 'Table'[Revenue] ),
        ALLEXCEPT ( 'Table', 'Table'[Region], 'Table'[Profit_Center] )
    )