Forum Discussion

DaneHowarth's avatar
DaneHowarth
New Member
4 years ago
Solved

Filtering a result based on another column

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.

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    DaneHowarth , Create a measure like

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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])
        )
    )
)