Forum Discussion

raphaelcamposs's avatar
raphaelcamposs
Regular Visitor
7 years ago
Solved

Relationship between tables with aggregation (Table Chart)

Hello Everyone.   I have 2 tables in PowerBI.   Table 1 (Keyword, Country, Webiste, ClickDate, NumClicks) Table 2 (Keyword, Country, Website, TotalDeposit, NumDeposit)   I need to create a t...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi raphaelcamposs,

     

    You could create below measure:

    Total NumClicks =
CALCULATE (
    SUM ( Table1[NumClicks] ),
    FILTER (
        Table1,
        Table1[Keyword] = LASTNONBLANK ( Table2[Keyword], 1 )
            && Table1[Country] = LASTNONBLANK ( Table2[Country], 1 )
            && Table1[Webiste] = LASTNONBLANK ( Table2[Webiste], 1 )
    )
)

    Then, create a table visual with information from Table 2 and above measure.

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi raphaelcamposs,

 

You could create below measure:

Total NumClicks =
CALCULATE (
    SUM ( Table1[NumClicks] ),
    FILTER (
        Table1,
        Table1[Keyword] = LASTNONBLANK ( Table2[Keyword], 1 )
            && Table1[Country] = LASTNONBLANK ( Table2[Country], 1 )
            && Table1[Webiste] = LASTNONBLANK ( Table2[Webiste], 1 )
    )
)

Then, create a table visual with information from Table 2 and above measure.

 

Best regards,

Yuliana Gu

  • raphaelcamposs's avatar
    raphaelcamposs
    Regular Visitor
    7 years ago

    Fantastic! It worked! Thank you so much!

  • YoungJim88's avatar
    YoungJim88
    New Member
    7 years ago

    Hi v-yulgu-msft ,

     

    Excellent reply. This is exactly what I was looking for.

     

    Best regards,

     

    James.