Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Sum/Total a MEASURE Column ignoring row filter

I feel bad asking this because I have done some solid intermediate dax and powerbi work, but this has stumped me.

 

I have a Measure called "Excess Loss" that calculates my excess loss over my insurance retention for each accident. (Think of it like stock options where each year the option price is different). Since it is a measure and not a calculated column, PowerBi is calculating the incorrect value for TOTAL Excess loss.

 

Here is a screenshot where the green highlights represent correct calculation, red is incorrect.

 

All I am trying to get is a single total for excess loss so I can make it into a KPI / CARD visual in PowerBi.  Is there a formula to calculate the sum of a current measured column?  

 

Need Help

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    Anonymous

     

    If the first 4 columns in the snapshot are from the source, to achieve the required output, you can follow below steps.

    1. In the Data page, create a new column "copy Column = [Current Insured Retention]", set the data type as whole number
    2. In the Data page, set the column [Current Insured Retention] date type as text.
    3. Create a measure "Excess Loss = SUM(Table[Total Cost])-SUM(Table[Copy Column])"
    4. Then in the report page.


     

    The only problem is the count of Claims total =242 in the snapshot, which looks obviously incorrect for me. Can you explain it?