Forum Discussion

dralexandero's avatar
dralexandero
Frequent Visitor
8 years ago
Solved

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:   The logic to calculate the result I...
Need Help
  • dralexandero's avatar
    dralexandero
    8 years ago

    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:

danextian's avatar
danextian
Icon for Super User rankSuper User
8 years ago

Hi dralexandero,

 

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

      • dralexandero's avatar
        dralexandero
        Frequent Visitor
        8 years ago

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