Filtering a result based on another column

[DaneHowarth]

Hi all

I am looking for some help to figure out the DAX required to filter a column by another column.  I have included some data below to illustrate the problem. 

In this example there are 3 companies (Alpha, Bravo, and Charlie). Alpha and Bravo are both to be compared against Bravo's sales (Reference Company). What i am trying to figure is how to calculate Reference Company Sales in the output.

I though the DAX might go as follows but it doenst work as A_Table[Company] doesnt return all rows, only the current row.

Reference Company Sales = CALCULATE(SUM[Sales],filter(A_Table, A_Table[Company]=[Reference Company]))

The example is below:

Thanks in advance for any help you are able to provide.

[tamerj1]

Hi @DaneHowarth 
I just noticed that my original solution deliveres correct results but wrong total. Here is the solution with the correct total https://www.dropbox.com/t/z9kBn81LzoPEG7Ex

Reference Company Sales = 
SUMX (
    SUMMARIZE ( A_Table, A_Table[Company], A_Table[Reference Company] ),
    CALCULATE ( 
        CALCULATE ( 
            SUM ( A_Table[Sales] ),
            REMOVEFILTERS(),
            TREATAS ( VALUES ( A_Table[Reference Company] ), A_Table[Company])
        )
    )
)

[tamerj1 · Accepted solution]

Hi @DaneHowarth 
Here is a sample file with the solution https://www.dropbox.com/t/T2GfvtNkOp8FqBl4

Reference Company Sales = 
CALCULATE ( 
    SUM ( A_Table[Sales] ),
    REMOVEFILTERS ( A_Table ),
    TREATAS ( VALUES ( A_Table[Reference Company] ), A_Table[Company] )
)

[amitchandak]

@DaneHowarth , Create a measure like

Reference Company Sales = CALCULATE(SUM(Table[Sales]),filter(allselected(A_Table), A_Table[Company]= max(Table[Reference Company])))

[DaneHowarth]

Awesome, thanks so much @amitchandak