Forum Discussion

freginier's avatar
freginier
Icon for Solution Sage rankSolution Sage
4 years ago
Solved

Sum each group with relation

Hi guys, 

 

I need your help ! I have 2 tables 

Table 1 :

NameCompanyDateValueA
User1Company101/01/202210
User2Company101/01/202217
User3Company201/01/202230

 

Table 2 : 

NameDateValueB
User101/01/20222
User201/01/20224
User301/01/20224
User301/01/20223
User201/01/20221

 

What I want : 

NameDateValueAValueA companyValueB
User101/01/202210272
User201/01/202217275
User301/01/202230307

 

How can I can get this ? I start "ValueA company" with this 

 

CALCULATE(SUM(ValueA),ALLEXCEPT(Table1,date,company)

 

 This formula works sometimes, depends columns I want to display..

For example, If I display "Name" from table2 it will work but if try to display "Name" from both tables the formula will display a very big result..  

 

Any help ? 

Thank you 

Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi freginier 

     

    You can add two custom columns to Table 1 with this DAX expressions:

     

    Value A company = 
CALCULATE (
    SUM ( 'Table 1'[ValueA] ),
    ALLEXCEPT ( 'Table 1', 'Table 1'[Company] )
)

     

    Value B = 
CALCULATE (
    SUM ( 'Table 2'[ValueB] ),
    FILTER ( ALL ( 'Table 2' ), 'Table 2'[Name] = EARLIER ( 'Table 1'[Name] ) )
)

     

    Output:

    Sample file attached, please download.

     

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

1 Reply

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi freginier 

     

    You can add two custom columns to Table 1 with this DAX expressions:

     

    Value A company = 
CALCULATE (
    SUM ( 'Table 1'[ValueA] ),
    ALLEXCEPT ( 'Table 1', 'Table 1'[Company] )
)

     

    Value B = 
CALCULATE (
    SUM ( 'Table 2'[ValueB] ),
    FILTER ( ALL ( 'Table 2' ), 'Table 2'[Name] = EARLIER ( 'Table 1'[Name] ) )
)

     

    Output:

    Sample file attached, please download.

     

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/