Forum Discussion

MichielvS's avatar
MichielvS
Frequent Visitor
9 years ago
Solved

Calculation over tables

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...
Need Help
Show and Tell
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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



     

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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