SUM with Filter returns the same value

[lencepeda]

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

[Eric_Zhang · Accepted solution]

---

@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]

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

[lencepeda]

Eric, thanks for your response.

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

Appreciate the quick response.  Len