Measure to create a sum of a column based on matched values from another table

[Anonymous]

I have a column for Channel and its viewership across different regions in Table 1. This is a weekly data.

In Table 2, I have Channel and its preferred region (one per channel).

I want to create a measure to show a graph which has channels and the sum of viewership for the preferred region. 

Can't find any solution. Need help.

[amitchandak]

@Anonymous ,

If you join, slicer should help

Else refer

https://docs.microsoft.com/en-us/dax/treatas-function

OR Can you share sample data and sample output.

[Anonymous]

Hi Amit,

Thanks for the response. I was actually looking to create a measure instead of a table.

The data looks something like this;

Table 2Table 1

[v-lili6-msft · Accepted solution]

HI  @Anonymous 

Assume that you have create a relationship between two tables by Channel field, then just use this formula to create a measure:

Measure = CALCULATE(SUM('Table 1'[Viewership]),FILTER('Table 1','Table 1'[Region] =RELATED('Table 2'[Preferred region])))

or

Measure 2 = CALCULATE(SUM('Table 1'[Viewership]),FILTER('Table 1','Table 1'[Region] =MAX('Table 2'[Preferred region])))

here is sample pbix file, please try it.

Regards,

Lin