Calculation over tables

[MichielvS]

I want to calculate the average churn, based upon two tables in my model, but with the possibility to filter the output in the report view through one or more dimensions. The content of the tables is: 

Table1

Clientnr          Month         Subscription   Product

A                     201501        1                     X

A                     201502        1                     X

A                     201501        1                     Y

B                     201501        1                     X

B                     201502        1                     Y

Table2

Clientnr          Month         Churn             Product

A                     201501        1                    X

A                     201502        0                 

B                     201501        1                    X

B                     201502        0

Resulting in a form of output where I can see that, if I filter the output for product X:

Month        Churn%

201501       100%

201502       0%

... and if I filter the output for product Y:

Month        Churn%

201501       0%

201502       0%

How do I do this in the preparation? I already have prepared:

Table11 = SUMMARIZE(Table1,Table1[Month],"count",SUM(Table1[Subscription]))
Table22 = SUMMARIZE(Table2,Table2[Month],"count",SUM(Table2[Churn]))

and

Churn% = Table22[count]/RELATED(Table11[count])

Do I have to incorporate 'Product' in the new tables? How?

[v-huizhn-msft · Accepted solution]

Hi @MichielvS,

You should incorporate "Product" column in new table, then you can lookup related value form Table22 in Table11.

I create new tables using the following formulas.

Table11 = SUMMARIZE(Table1,Table1[Month],Table1[Product],"count",SUM(Table1[Subscription]))

Table22 = SUMMARIZE(Table2,Table2[Month],Table2[Product],"count",SUM(Table2[Churn]))

Table11


Table22


Then create a calculated to get related Table22[count] based on month and product columns. If there is no corresponding products, it will return 0.

Related Table22 = IF(ISBLANK(LOOKUPVALUE(Table22[count],Table22[Month],Table11[Month],Table22[Product],Table11[Product])),0,LOOKUPVALUE(Table22[count],Table22[Month],Table11[Month],Table22[Product],Table11[Product]))

Finally create another column to get Churn% result.

Churn% = Table11[Related Table22]/Table11[count]

Create a slicer including Table11[Product] field, and a table visual to display the desired result.

If you have other issues, please feel free to ask.

Best Regards,
Angelia