PowerBI - Linked sources - splicer not working - expanded rows

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
135
Office Version
  1. 365
Platform
  1. Windows
Apologies for another question, but I've been trying for hours to resolve this with no luck...I've done my best to capture what my issue is. In summary I have tried to apply a splicer from one table against data in another table and the results are incorrect. I *think I've created the relationships correctly. And I believe it is as a result of expanding rows within my data, but I can't see to fix it. Here is the detail:

I have three tables from an online SharePoint List within PowerBI. I've managed to transform the data, which involved expanding many of the fields in some of the tables to multiple rows due to there being multiple entries in each record. I have managed to set up the relationships between the tables i.e. 1-many, many-many etc, but I'm having trouble using splicers where the data is from one table and the splicer is from another. My tables are as follows:

  1. Organisation list (listing all organisations we work with and info about them - each row is unique with a unique identifier that is not repeated)
  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) There is a unique identifier in this list, but I have had to expand some rows because of multiple choices in some fields)
  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) There is a unique identifier, but I have had to expand the rows because there are numerous fields with multiple choices which I needed to expand)
Relationships:
Each table has a unique ID and in the original lists, they work using lookups between each one
  • Organisation to Activity = one:many (An activity can have many organisations in attendance)
  • Organisation to Phonebook = one:many
  • Phonebook to Activity = many:many (because there are repeated rows in the phonebook and activity lists - one activity can also have many attendees from the phonebook)

A further complication is that because I have expanded the rows in the Activity List, one of my fields with numbers (number of attendees) was therefore repeated numerous times and therefore didn't have the correct sum when I was visualising the data. I got around this by counting the repetition of IDs and dividing each number by the number of times it was repeated - following this advice Link to previous question

The problem I now have is that when I visualise data from the Activity list and use a splicer from the organisation list, the number of attendees doesn't present correctly. What it appears to be doing is just taking the first entry for each activity, ever though it might be repeated numerous times - due to the expansion of rows. For instance, if I applied the splicer so it filters to only high street stores (dummy data), I should have the following rows summed to make 14 attendees.

Activity IDOrganisation NameAttendees (number divided by the number of repeated rows)
1Pizza Express1.666
1KFC1.666
1McDonald's1.666
2Wilko1.5
2Subway1.5
3Target2
3Starbucks2
3Costco2
14
However, when the splicer is applied, it only takes the first value of each activity e.g. 1.666 + 1.5 + 2 = 5.166.

Can anyone help me resolve this?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top