Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dralexandero
Frequent Visitor

Filters won't work for visuals from calculated tables

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:

001.png

 

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:

002.png

 

And summarized policy count for each month under every bucket:

003.png

 

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.

004.png005.png007.png

 

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

1 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. Smiley Happy

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

Hi @dralexandero,

 

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










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

 

 

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

 

policy.png 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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. Smiley Happy

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). 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.