cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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?  

 

Total Column wrong.png

2 REPLIES 2
Eric_Zhang
Microsoft
Microsoft

@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.
    Capture.PNG

 

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

mike_honey
Memorable Member
Memorable Member

Post your DAX formula.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!