Forum Discussion

Williamkb's avatar
Williamkb
Frequent Visitor
3 years ago

Calculate values from different rows in the same column into a different column

Entry DateIDValueTotal
1/1/2022AA18
1/2/2022AB29
1/3/2022AC30
1/4/2022AD44
1/5/2022BA58
1/6/2022BC66
1/7/2022BD77
1/8/2022BB88
1/9/2022CC44
1/10/2022CA30
1/11/2022CB66
1/12/2022CD77
1/13/2022BB90
1/14/2022CC20
1/15/2022DD11
1/16/2022FC66
1/17/2022FD77
1/18/2022AA80

 I need to add 2 or more rows together from the Value column and put the results into the Total column.

For Example in the above table 'AA' and 'BD' were added together and the results put in 'AA' Total row. Notice that the Value in the 'BD' row was added to nothing and placed in the 'BD' Total column. Since ID can repeat I need to make sure to only get the Value from the latest date.

Also 'AB' and 'CD' was added together and the results placed in the 'AB' Total row, etc ...

Extra info: The ID's that add together always are added together and I always need to get the latest data date per ID. This data goes back 6+ years and has hundreds of thousands of rows.

 

Any help would be appreciated. I am relatively new to PBI so please try to keep it simple and explain in detail.

 

Thank you.

Need Help

3 Replies

  • Williamkb's avatar
    Williamkb
    Frequent Visitor
    3 years ago

    I tried something like this but it didn't actually add the 2 values together. I'm hoping there is somone out there who likes a challenge and can help me out with this.

     

    IF(FIRSTNONBLANK('External Data'[Dataset ID], 1)="AA",
    CALCULATE(
    CALCULATE(
    SUM('TABLE'[Value]),
    FILTER(
    'TABLE',
    CONTAINS('TABLE', 'TABLE'[ID], "AA")
    ),
    FILTER(
    'TABLE', 'TABLE'[Entry Date]=MAX('TABLE'[Entry Date])
    )
    ) +
    CALCULATE(
    SUM('TABLE'[Value]),
    FILTER(
    'TABLE',
    CONTAINS('TABLE', 'TABLE'[ID], "BD")
    ),
    FILTER(
    'TABLE', 'TABLE'[Entry Date]=MAX('TABLE'[Entry Date])
    )
    ),
    FILTER(
    'TABLE', 'TABLE'[Entry Date]=MAX('TABLE'[Entry Date])
    )
    ),
    CALCULATE(
    SUM('TABLE'[Value]),
    FILTER(
    'TABLE', 'TABLE'[Entry Date]=MAX('TABLE'[Entry Date])
    )
    )
    )

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    This is doable but what is the logic to decide which IDs to combine? Why do AA and BD go together? Is there another column not provided? Also, this could be done in Power Query or with DAX. Power Query would be recommended.

     

    Pat

     

    • Williamkb's avatar
      Williamkb
      Frequent Visitor
      3 years ago

      Hi Pat,

      Thank you for responding.

       

      Unfortunetly the logic to which ID's are combined is human choice. This data is pulled from a government public site and represents different sections/business sectors/economic key points of the US economy. My customer has selected which ID's pretains to their business and therefore which ID's should be combined (added, subtracted or averaged).

       

      The good news is the ID's are consistent, so AA and BD will always be added together. Once the logic of the code is hammered out it will be rare for changes to occur.

       

       Thank you again for your help.

      William