Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |