Summation of values in rows with same ID

[PBI-Beginner]

Hi All,

I am a fairly new user of PBI and have spent all day on the forum trying to do something rather simple.

I am trying to come up with a DAX measure to sum the "RECORDED_VALUE" of rows with the same "ID". Below is a snippet:

This is the DAX measure I have been using which I came up with after guidance on this forum:

I am at my wits' end, and cant figure out why I am getting a value of 1,208.19 (first four rows with same ID) for something that should be (27+26.14+25.70+26.18=105.02).

Any help will be appreciated!

Regards

[Tad17]

You need to use the SUMX formula. It is the SUMIF of Power BI. See the link below:

https://docs.microsoft.com/en-us/dax/sumx-function-dax

It should be as simple as = SUMX('Table'[ID],'Table'[RECORDED_VALUE])

Depending on how your data is set up you may need some other functions in there but the link should have examples of everything you could need. If your data is in two separate tables you can refer to this link: https://community.powerbi.com/t5/Desktop/Sumif-between-two-tables/td-p/333352

[littlemojopuppy]

This should be a simple SUM statement.  Measure = SUM(Recorded_Value)

Using that measure in the table should summarize to include only those records with a given ID because of filter context.  Try it out...

[littlemojopuppy]

Instead of ALLEXCEPT, try SELECTEDVALUE

[PBI-Beginner]

Hi,

Thanks for your quick reply.

Modified it as follows:

SUMBYID = CALCULATE(SUM(QUERY0002[RECORDED_VALUE]), SELECTEDVALUE(QUERY0002, QUERY0002[ID]))

Getting this error now:

A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[littlemojopuppy]

Sorry...I was incorrect and I misinterpreted your question.

This should be a simple SUM statement.  Measure = SUM(Recorded_Value)

Using that measure in the table should summarize to include only those records with a given ID because of filter context.  Try it out...

[PBI-Beginner]

Above is the result after using:

SUMBYID = SUM(QUERY0002[RECORDED_VALUE])

No luck, it is showing the same value as the Recorded Value, hasnt done any summation it appears.

The image in the original post shows a snippet of the data, but the table even after applying filters shows rows with other IDs, its how the report has been set up.

[littlemojopuppy]

Are you sure that the ID field has no leading or trailing spaces...that they are in fact the same value?

And can you share the PBIX?  Or dump some raw data into an Excel file?

[PBI-Beginner]

I apologise, I cant share the PBIX as its confidential data 😞 but below is a snippet of the ID numbers, its a concatenation of two other fields and is alphanumeric in nature and the number of digits isnt fixed either, it is formatted as a text field on PBI.

I will try to trim and clean it, but as far as I can remember I have done that previously. Below is a snippet of the ID column.

Sorry for the not being able to share the file, any suggestions will be appreciated.

[littlemojopuppy · Accepted solution]

Understand about not being able to share confidental data in the PBIX.  If you could cut and paste 50-100 records of only the ID and recorded value into Excel that would be helpful.

One thing I'm curious about...how did you get frequency in your table?  Because that seems to be behaving exactly as expected.  Have you tried duplicating whatever you did there?

[PBI-Beginner]

@littlemojopuppy Cannot believe I didnt try that until now, that has worked a charm and I dont even know why this has and not what I was trying!

I repurposed my frequency count formula to get this:

And it did the trick.

Need to figure out why the others havent worked though, so I understand better for next time. Thanks for your help mate.

@Tad17 Thanks for the links, I willl give it a read. New to PowerBI so this helps! Thank you!

[littlemojopuppy]

De nada, but I don't think that I did much to help 😉

[littlemojopuppy]

Hi!  It was annoying me why this wasn't working so I created some fake data in Excel that mirrored yours.  I understand why what I was saying was completely wrong...

The filter context of the table is for both ID and Recorded Value and not just ID.  So using a simple sum statement was going to total (in my sample data) where ID = A and RecordedValue = 64, etc.  And of course it would return the exact same number (assuming no duplicates in RecordedValue).

  

Sorry for wasting your time...I should have thought about this more closely.  Apologize for wasting your time!