cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

calculation

Spoiler
 

I want to calculate the average churn, based upon two tables in my model, containing:

 

 

Table1

Clientnr          Month         Subscription

A                     201501        1

A                     201502        1

B                     201501        1

B                     201502        1

 

Table2

Clientnr          Month         Churn

A                     201501        1

A                     201502        0

B                     201501        1

B                     201502        0

 

Resulting in a form of output where I can see that:

Month        Churn%

201501       100%

201502       0%

 

How do I do this in the report view?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: calculation

Hi @MichielvS,

Based on my understanding, for 201501 month, there is 2=1+1(AandB) Churn in Table2, 2=1+1(AandB) Subcription in table1, so 100%=2/2. And for 201502 month, there is 0=0+0(A and B) Churn in Table2, 2=1+1(A and B) Subcription in Table1, so 0%=0/2, right? If it is, please review the following steps.

First, click New table under Modeling in home page, create two new tables using the following formulas.

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

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

 

Please see the new tables.

 

1.PNG

 

Capture2.PNG


Then create relationship between Table11 and Table22.

Capture12.PNG

In table22, create a calculated column using the formula, create a table visual to show the expected result.

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


20.PNG

Capture21.PNG
If this is not what you want, please let me know.

Best Regards,
Angelia

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: calculation

Hi @MichielvS,

Based on my understanding, for 201501 month, there is 2=1+1(AandB) Churn in Table2, 2=1+1(AandB) Subcription in table1, so 100%=2/2. And for 201502 month, there is 0=0+0(A and B) Churn in Table2, 2=1+1(A and B) Subcription in Table1, so 0%=0/2, right? If it is, please review the following steps.

First, click New table under Modeling in home page, create two new tables using the following formulas.

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

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

 

Please see the new tables.

 

1.PNG

 

Capture2.PNG


Then create relationship between Table11 and Table22.

Capture12.PNG

In table22, create a calculated column using the formula, create a table visual to show the expected result.

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


20.PNG

Capture21.PNG
If this is not what you want, please let me know.

Best Regards,
Angelia

View solution in original post

Highlighted
Frequent Visitor

Re: calculation

This is exactly what I was looking for! Thank you very much Angelia, your explanation is very helpful.

Highlighted
Frequent Visitor

Re: calculation

Now I would like to take this one step further, if possible. Do you know if I can incorporate dimensions to use as slicers, in one way or another? With the current result I can only show the totals over the base. What if for instance I would have the field 'gender' that I would like to be able to filter on in the output? Is that possible at all?

Highlighted
Microsoft
Microsoft

Re: calculation

Hi @MichielvS,

Based on your description, it's difficult to reproduce the scenario. Unusualy there is one question in one case. I personally suggest you open a new thread and post the sample data and expexted result. Thanks for your understanding.

Best Regards,
Angelia

Highlighted
Frequent Visitor

Re: calculation

I will do that, thanks.

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors