Filters won't work for visuals from calculated tables

[dralexandero]

Hello, I'm new to PowerBI and I'm trying to create a report for an insurance Policy Life Expectancy calculation.

The dataset is something like below:

The logic to calculate the result I want is based on summarized tables from the original dataset:
Summarized active days for each month under every persistency bucket:

And summarized policy count for each month under every bucket:

Next, I divide the active days by active policies to arrive at active days per policy; I then multiply these values by the triangle of lifetime retention rates (the remaining row % from summarized policy table); finally, I sum up the numbers on the diagonal to arrive at a total capped Policy Life Expectancy for each month.

The final result will be a simple table shows the Policy Life Expectancy per month. I was able to do this by creating a measure from the summarized tables. However, the slicers based the original dataset won't work for the visuals becaused the calculated table won't be affected by the slicers. (such as multicar or not, rewrite or not).

Alternatively, I tried to write R script to solve this problem, but it seems there's a limitation of 150k rows.

I'm wondering if there is any approach to generate visuals from a temporary table that can be affected by filters from original dataset, or I can directly create a measure to sum a measure grouped by bucket based on the original dataset? Any help would be appreciated!

Regards,
Ray

[Ashish_Mathur]

Hi,

I am interested in helping out.  Could you share the business problem first.  Thereafter in a simple Excel file, please explain all calculated steps that you would have performed.  This will help me build the formulas in PowerBI desktop.

[dralexandero]

Hi, @Ashish_Mathur, thanks for your interest in this.

The business problem is to calculate the insurance Policy Lifetime Expectancy and here is the link for a sample dataset:

https://drive.google.com/file/d/1e1gJ0OXEp--c_QMaDxLVKH_zVidAL5Pt/view?usp=sharing

The calculation steps are described as below (let's say the 6 screenshots in my first post are labeled as Fig. 1 ~ Fig. 6):

(1) Summarize the sum of policyActiveDays by date and persistencyBucket (as Fig. 2), and then summarize the sum of policyDistinctCount by date and persistencyBucket (as Fig. 3).

(2) Divide the values in in Fig. 2 (policyActiveDays) by values in Fig. 3 (policyDistinctCount) to arrive at active days per policy (as Fig. 4).

(3) Divide the values in Fig. 3 (policyDistinctCount) by the leftmost values in every row (persistencyBucket = 0) to arrive at lifetime retention rates (as Fig. 5).

(4) Multiply these values in Fig. 4 (active days per policy) by the values in Fig. 5 (lifetime retention rates) to arrive at fractional Policy Life Expectancy (as Figure 6).

(5) Calculate the sum of values on the diagonal in Fig. 6 to arrive at a total Policy Life Expectancy for each month.

My question is, I was able to achieve the PLE by doing calculation on a summarized table. However, the filters (multicar, rewrite, etc.) are not working because the result is from the static calculated table. Is there a way to do this calculation without a summarized table? Thanks!

[danextian]

Hi @dralexandero,

Would you mind attaching an Excel file of your sample table or pasting a HTML table instead of an image?

[dralexandero]

Hi @danextian, attached is the sample csv file with 2000 rows. Thanks.

https://drive.google.com/file/d/1e1gJ0OXEp--c_QMaDxLVKH_zVidAL5Pt/view?usp=sharing

[danextian]

Hi @dralexandero, Not sure if this is the solution  you're looking for but this might help.

https://drive.google.com/open?id=14tCau_ehtEA6606v2sJHOC2tEUpn27tz

 

[dralexandero · Accepted solution]

Hi @danextian, I just figured out how to calculate that. Firstly, I created a measure to calculate the ActiveDays/PolicyCount for each date and bucket. Next, I transformed the diagonal values into row values by using a new row index. Finally, I used the DAX "SUMX(VALUES(Table[date]), [Measure])" to calculate the SUM of each row and got what I need. Thanks, though.

[dralexandero]

Hi @danextian, thanks for your help. Your solution is very close to what I want - the sum of numbers in each row of the table from your screenshot. Currently, the row subtotal in the matrix is not a simple sum of each number, but a calculation based on the total active days divided by the total policy counts. In other words, I'm wondering if we can have a measure to calculate the SUM(active days)/SUM(policy counts) for each bucket and each month first (numbers in your table), then we could again calculate the sum of these numbers for each month (sum of row total).