cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lencepeda Frequent Visitor
Frequent Visitor

SUM with Filter returns the same value

Hi all,

 

I am playing around with a measure intended to sum up a currency value (Expiring Policy Gross Premium) filtered if a value appears in another column, "Lost Renewal Count"

 

Sample Data 

 

UserID      Trans    Current Premium     Expiring Premium   Lost Renewal Count

Bob           Z1           1000                         1500                       0

Joe            R2            0                              2000                       1

Bob           A1            0                              1200                       1

Dan           A2            0                              1000                       1

 

 

SumExpiringLostRenewalPremium = CALCULATE(SUM(LostRenewalsLarge[Expiring Policy’s Gross Premium]),FILTER(Cube_SubmissionActivity_All,'Cube_SubmissionActivity_All'[LostRenewalCount] = 1))

 

The problem is when this is presented in a Matrix table the same value is returned at every line, the Total Expiring Gross Premium

 

User ID      Expiring Gross Premium

Bob            4200

Joe             4200

Dan            4200

 

in lieu of 

 

Bob        1200

Joe         2000

Dan        1000

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: SUM with Filter returns the same value


@lencepeda wrote:

Hi all,

 

I am playing around with a measure intended to sum up a currency value (Expiring Policy Gross Premium) filtered if a value appears in another column, "Lost Renewal Count"

 

Sample Data 

 

UserID      Trans    Current Premium     Expiring Premium   Lost Renewal Count

Bob           Z1           1000                         1500                       0

Joe            R2            0                              2000                       1

Bob           A1            0                              1200                       1

Dan           A2            0                              1000                       1

 

 

SumExpiringLostRenewalPremium = CALCULATE(SUM(LostRenewalsLarge[Expiring Policy’s Gross Premium]),FILTER(Cube_SubmissionActivity_All,'Cube_SubmissionActivity_All'[LostRenewalCount] = 1))

 

The problem is when this is presented in a Matrix table the same value is returned at every line, the Total Expiring Gross Premium

 

User ID      Expiring Gross Premium

Bob            4200

Joe             4200

Dan            4200

 

in lieu of 

 

Bob        1200

Joe         2000

Dan        1000


@lencepeda

I see two tables in your DAX while there's one is described in your post. Usually, one possible reason for the "Expiring Gross Premium" showing the same value for all rows is a improper relationship between those two tables. What is the relationship in your case? For further suggestion, could you post more details of another table, even better your can share a pbix file(upload it to Onedrive/Goodle drive and share the link).

3 REPLIES 3
Moderator Eric_Zhang
Moderator

Re: SUM with Filter returns the same value


@lencepeda wrote:

Hi all,

 

I am playing around with a measure intended to sum up a currency value (Expiring Policy Gross Premium) filtered if a value appears in another column, "Lost Renewal Count"

 

Sample Data 

 

UserID      Trans    Current Premium     Expiring Premium   Lost Renewal Count

Bob           Z1           1000                         1500                       0

Joe            R2            0                              2000                       1

Bob           A1            0                              1200                       1

Dan           A2            0                              1000                       1

 

 

SumExpiringLostRenewalPremium = CALCULATE(SUM(LostRenewalsLarge[Expiring Policy’s Gross Premium]),FILTER(Cube_SubmissionActivity_All,'Cube_SubmissionActivity_All'[LostRenewalCount] = 1))

 

The problem is when this is presented in a Matrix table the same value is returned at every line, the Total Expiring Gross Premium

 

User ID      Expiring Gross Premium

Bob            4200

Joe             4200

Dan            4200

 

in lieu of 

 

Bob        1200

Joe         2000

Dan        1000


@lencepeda

I see two tables in your DAX while there's one is described in your post. Usually, one possible reason for the "Expiring Gross Premium" showing the same value for all rows is a improper relationship between those two tables. What is the relationship in your case? For further suggestion, could you post more details of another table, even better your can share a pbix file(upload it to Onedrive/Goodle drive and share the link).

lencepeda Frequent Visitor
Frequent Visitor

Re: SUM with Filter returns the same value

Eric, thanks for your response.

 

Thank you for catching my error.  I accidentally referenced another table in the measure.

 

Appreciate the quick response.  Len

lencepeda Frequent Visitor
Frequent Visitor

Re: SUM with Filter returns the same value

Hi Eric, thanks for catching the error.  I accidentally referenced the same column name from another table in the measure.  Thank you