How can I sum all rows that have the same ID and Date?

[Anonymous]

Hi all,

What I am trying to do should be very simple. I have a Table holding an ID, NAME, DATE, VALUE and STATUS.

The table could look something like this:

- ID & NAME will always be the same in pairs. 

- DATE can be different for every pair.
- VALUE should be summed together for each ID & NAME pair.

- Status should be taken from a row that doesn't have value 99 (it's a palceholder in my program)

In the end the table should look like this:

I have colored the old and new row(s) to see how they transform.

Is what I want to achieve possible? 

Thank you for the help.

[AlB · Accepted solution]

Hi @Anonymous 

1. Place ID, Date and Name in a table visual

2. Place these measures in the visual

ValueM =
SUM(Table1[Value])

StatusM =
CALCULATE ( DISTINCT ( Table1[Status] ), Table1[Status] <> 99 )

Note [StatusM] assumes there's only 1 Status value different from 99 per ID-Name-Date

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hey @AlB,

This works absolutely perfect!

The StatusM didn't quite work for me (as you said, more than 1 Status different from 99 would not work, which I have). But I still got it to work; I just put the Status itself in the table and because it's always the first row that holds the Status that works.

So really all I needed was ValueM. 

Thank you very much for the help 🙂

Edit: I have no idea how it worked when I wrote this comment, but since then the Status doesn't work anymore. It's just the sum of all together, which is obviously wrong.

I tried to put the Measure in a MIN() or FIRSTNONBLANK() to just select one value, but with no success. 

Edit2: I got it working again. I simply had to unselect "Sum" from Status in the Visualization window in the report. With this I also got the sum of VALUE without having to create a measure. 

For any future visitors: Select Table -> Visualizations Tab -> Right Click your Column under Values -> Click "Sum". And for the Status select "Don't summarize" in the last step.