PowerBI expand to new rows for fields with multiple entries causing problems with summing number fields

[Tashat]

Hi all

I'm VERY new to PowerBI, but have been working in Excel for forever, so have some knowledge on logic etc. I should say that I would rather have a CRM or database for what I'm doing, but I can't and it's a very long, frustrating, corporate story - we are where we are!

I have datasets I've imported to PowerBI desktop which I want to analyse, interrogate etc. from three online SharePoint lists:

1. Organisation list (listing all organisations we work with and info about them)
2. Phonebook list (with individual contacts working at the organisations - I have a lookup field allowing my users to select which organisation an individual works for - which pulls from the previous list)
3. Activity list (every time we email or have contact with someone / and organisation we record the activity here. There are lookup fields to both of the previous lists

In addition to the above, there are a number of fields (including the lookup fields) where the user can select multiple options. e.g. multiple contacts from the phonebook could be involved in a single activity in the activity list. Similar with the organisations.

I have therefore imported my data and transformed the data to expand to new rows for each of these instances where the user could have made multiple selections. I need to do this, because I want to create tables and charts etc which allow me to group info together - e.g. look at all the data for a single organisation. However, I also have some fields which require the user to enter numbers. Now of course, when I've expanded the rows, it has repeated all these numbers down the full list. When I come to Sum these numbers, the resulting value is incorrect because of duplicate counting.

e.g. I've added a data card, selected to sum the relevant field e.g. number of attendees and of course it is much larger than it should be. Can anyone help me with this. The count action is fine because I've done "Count distinct", but can't seem to do this for the sum.

 

[RoryA]

If you add a column (prior to splitting to new rows) that has a count of the entries in the field you are about to split, you can divide the value field(s) by the count and then split out. That way the overall sum should be correct.

 

[Tashat]

If you add a column (prior to splitting to new rows) that has a count of the entries in the field you are about to split, you can divide the value field(s) by the count and then split out. That way the overall sum should be correct.

Hi thank you for this and taking the time to reply I really appreciate it - that makes sense to me. However, I have around 10 fields/columns which this applies to where there are multiple selections in each record/cell. So in that instance, I'm not sure how I would be able to add a column which counts all the entries from 10 different fields? Is there a way?

Alternatively, I do have an ID field though which is obviously a unique identifier, so would it work to add the column after I've done the expansion to split and then do some formula to count the number of occurrences of each ID? e.g. after splitting I now have 15 rows all for record ID 4, and then 10 for ID 5 etc.

 

[RoryA]

Yes, that might be simpler if you have multiple fields to split, especially if not all might be populated for every row.

 

[Tashat]

Yes, that might be simpler if you have multiple fields to split, especially if not all might be populated for every row.

ok thank you - is there any chance you might be able to help me with the formula?

 

[RoryA]

There are many ways to do it. For example, you can group the table on the ID column and choose All rows. That will produce a table of records for each ID. You can then use Table.RowCount in a new custom column to get the number of rows in each table, then just expand the table column to get all the data again.

 

[Tashat]

There are many ways to do it. For example, you can group the table on the ID column and choose All rows. That will produce a table of records for each ID. You can then use Table.RowCount in a new custom column to get the number of rows in each table, then just expand the table column to get all the data again.

That's perfect! I managed to get it to work and this is helping me to slowly learn PowerBI, so thank you very much!