Forum Discussion
Filters won't work for visuals from calculated tables
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. :smileyhappy:
Super UserHi,
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.
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!