Forum Discussion
Helper IVMerging tables with large numbers of rows
There's a typo in your expected result - g is appearing 10 times not 9 times. So the total is 911, not 902.
You may want to try TREATAS as an alternative, it will likely perform better with larger data sets
Helper IVHi - OK so if this is my sample data
Table A - lists the Groups
| Group |
| B |
| A |
| B |
| A |
| B |
| C |
| B |
| A |
| D |
| A |
| B |
| D |
| D |
Table B - lists the Items that make up each Group and their value
| Group | Item | Value |
| A | a | 10 |
| A | b | 15 |
| A | d | 22 |
| A | e | 18 |
| A | g | 9 |
| B | a | 10 |
| B | b | 15 |
| B | d | 22 |
| B | g | 9 |
| B | h | 3 |
| C | b | 15 |
| C | c | 25 |
| C | d | 22 |
| C | f | 6 |
| C | g | 9 |
| D | a | 10 |
| D | c | 25 |
| D | d | 22 |
| D | e | 18 |
| D | f | 6 |
If I were to expand out the Groups into their component Items and summarise the result I'd get this:
| Item | Count | Value Sum |
| a | 12 | 120 |
| b | 10 | 150 |
| c | 4 | 100 |
| d | 13 | 286 |
| e | 7 | 126 |
| f | 4 | 24 |
| g | 9 | 81 |
| h | 5 | 15 |
| Grand Total | 64 | 902 |
I want to then put it in a scatter to look a bit like this:
'
I can't work out how to get it to sum up all the items for each Group on the X axis - it just sums up what's in Table B when I do it, doesn't take table A into account to account for multiple Groups.
ie - in Table B, which is like the Dimension table, Item a appears 3 times. PBI is giving me 3 lots of 10 equals 30 as the sum, rather than 12 lot of 10 equals 120 - Item a appears 12 times if all the Groups from Table A were to be expanded out.
Thanks in advance for any help,
Super UserThere's a typo in your expected result - g is appearing 10 times not 9 times. So the total is 911, not 902.
You may want to try TREATAS as an alternative, it will likely perform better with larger data sets
- naninamu
Just on your point actually - do you mean use TREATAS to merge the 2 data sources? I did try this, but try as I might I couldn't get the syntax to work so I abandoned that path! Could you help with what that code might look like? Cheers.