Create measure to sum a column with multiple values from another dataset

[Anonymous]

Hi,

I have 3 tables. 

Channel subscribers are individual subscribers. I want to create a measure which calculates total subscribers for each channel. Total subscribers = Channel subscribers + Pack subscribers. Pack subscribers will be the subscribers in the pack where the channel is present. 

e.g. Total subscribers for BBC = 100 (Individual channel subscribers) + [ 500 (Silver) + 200 (Platinum) ] = 800

similarly for CNN = 200 + (200 + 300 + 500) = 1200 as it is available in all the packs, so subscribers of all the packs will be added. 

There are many such channels and even more packs. Can someone please help me in creating a measure.

Also, the two subscriber tables (channel and packs) are not directly related to mapping table. There is a pack master and channel master table which are linked by pack name and channel name respectively to these subscriber tables. 

However, mapping table is linked to both master tables by channel and pack name.

@v-lili6-msft 

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

First create a column in table mapping:

Column = LOOKUPVALUE('Pack subscriber'[Pack subscriber],'Pack subscriber'[Packs],'Mapping'[Pack],blank())

Then create a measure as below:

Measure = 
var _total=SUMX(FILTER('Mapping','Mapping'[Channel]=MAX('Mapping'[Channel])),'Mapping'[Column])
Return
SUM('Channel subscribers'[Channel subscribers])+_total

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Mariusz]

Hi @Anonymous 

Try this.

Measure = 
SUM( 'Channel subscribers'[Channel subscribers] )
+ SUMX( Mapping, RELATED( 'Pack subscribers'[Pack subscribers] ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi Mariusz, I am not getting the pack subscribers table in the related function in sumx. I think this is because tables are not directly related. They are related through master tables indirectly. Getting stuck here.