Forum Discussion

CarlSagan's avatar
CarlSagan
Icon for Helper II rankHelper II
1 year ago

Many to many relationship causing summation to rollup incorrectly

I have a dimension table called investment_selections and a transactional table called assets. When I put asset records in a table that does not contain investment_selection properties, the running total is summing up as expected. When I throw the assets into a visual that takes the summation of all the assets broken down by a column on the investment_selection table, rows are duplicated somewhere causing the overall sum to be inflated. Below is a screenshot of what I'm talking about

these two tables are joined on a concatenated column that takes the planid and account number of both the investment selection and asset records. I thought this would eliminate the many to many problem, but it doesn't seem like it did properly. Any insight on how to deal with many to many or a dax formula that can help point me in the right direction is greatly appreciated.

Need Help

2 Replies

    • CarlSagan's avatar
      CarlSagan
      Icon for Helper II rankHelper II
      1 year ago

      Simple

      assets_running_total_date_table = 
CALCULATE(
    [assets_total],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)