Forum Discussion
Sum values with condition from diferent tables
Hello:
I have 12 tables, each for one month. Here you have an example of two months. In the first column I have name and surname of each person. In following columns, I have different concepts that have earned all the days in the month. There was an additional day row that has been hidden. This is the reason because concepts are repeated en each month. Each one was earned in different day.
I need to sum the total of year of each concept per person. The conpcets can be different in each month. Anyobody knows how to calculate it?
Thanks in advance.
An example:
The desired result:
Year
| Junta Govern Local | Comissió Interàrees | |
| Person 1 | XXX | YYY |
| Person 2 | ZZZZ | KKKK |
Data I have:
January
| Junta Govern Local | Comissió Interàrees | Comissió Interàrees | Consell Escolar Mpal | Junta Govern Local | Comissió Interàrees | Comissió Informativa P | Junta de Portaveus | Comissió Interàrees | Consell Comerç i Consum | Junta Govern Local E | Ple | |
| Person1 | 61.48 | ABSENT | ABSENT | null | 61.48 | ABSENT | 112.7 | null | 175.24 | null | 61.48 | 276.62 |
| Person2 | null | 175.24 | 175.24 | null | null | 175.24 | 112.7 | null | 175.24 | 112.7 | null | 276.62 |
| Person3 | 61.48 | 175.24 | 175.24 | null | 61.48 | 175.24 | 112.7 | null | 175.24 | null | 61.48 | 276.62 |
| Person4 | 61.48 | ABSENT | 175.24 | 112.7 | 61.48 | ABSENT | ABSENT | 61.48 | 175.24 | null | 61.48 | 276.62 |
| Person5 | null | 175.24 | 175.24 | null | null | 175.24 | 112.7 | null | 175.24 | null | null | 276.62 |
| Person6 | null | null | null | null | null | null | 112.7 | 61.48 | null | null | null | 276.62 |
| Person7 | null | null | null | null | null | null | 112.7 | null | null | null | null | 276.62 |
| Person8 | null | null | null | null | null | null | 112.7 | null | null | null | null | 276.62 |
| Person9 | null | null | null | 112.7 | null | null | 112.7 | null | null | 112.7 | null | 276.62 |
| Person10 | null | null | null | null | null | null | 112.7 | null | null | null | null | 276.62 |
| Person11 | 61.48 | 175.24 | 175.24 | 112.7 | 61.48 | 175.24 | 112.7 | 61.48 | 175.24 | null | ABSENT | 276.62 |
| Person12 | null | 175.24 | 175.24 | null | null | 175.24 | 112.7 | null | 175.24 | 112.7 | null | 276.62 |
| Person13 | null | null | null | null | null | null | 112.7 | null | null | null | null | 276.62 |
| Person14 | null | null | null | 112.7 | null | null | 112.7 | 61.48 | null | 112.7 | null | 276.62 |
| Person15 | null | null | null | 112.7 | null | null | 112.7 | 61.48 | null | 112.7 | null | 276.62 |
February
| Cognoms i nom | Junta Govern Local | Comissió Interàrees | Comissió Interàrees | Junta Govern Local | Comissió Informativa P | Comissió Interàrees | Junta de Portaveus | Ple | Comissió Interàrees |
| Person1 | 61.48 | 175.24 | 175.24 | 61.48 | 112.7 | 175.24 | null | 276.62 | 175.24 |
| Person2 | null | 175.24 | 175.24 | null | 112.7 | 175.24 | null | 276.62 | 175.24 |
| Person3 | 61.48 | 175.24 | 175.24 | 61.48 | 112.7 | 175.24 | null | 276.62 | 175.24 |
| Person15 | 61.48 | 175.24 | 175.24 | 61.48 | 112.7 | 175.24 | 61.48 | 276.62 | 175.24 |
| Person18 | null | 175.24 | 175.24 | null | 112.7 | 175.24 | null | 276.62 | 175.24 |
| Person19 | null | null | null | null | 112.7 | null | ABSENT | 276.62 | null |
| Person20 | null | null | null | null | 112.7 | null | null | 276.62 | null |
| Person5 | null | null | null | null | 112.7 | null | null | 276.62 | null |
| Person6 | null | null | null | null | 112.7 | null | null | 276.62 | null |
| Person7 | null | null | null | null | ABSENT | null | null | 276.62 | null |
| Person8 | 61.48 | 175.24 | 175.24 | 61.48 | 112.7 | 175.24 | 61.48 | 276.62 | 175.24 |
| Person9 | null | 175.24 | 175.24 | null | 112.7 | 175.24 | null | 276.62 | 175.24 |
| Person10 | null | null | null | null | 112.7 | null | null | 276.62 | null |
| Person11 | null | null | null | null | 112.7 | null | 61.48 | 276.62 | null |
| Person12 | null | null | null | null | 112.7 | null | 61.48 | 276.62 | null |
1 Reply
- Daniel29195
Community Champion
Hello francescjp ,
i would suggest at first to unpivot the 12 tables :
example of unpivoting a table :
after unpivoting :
after you unpivot all tables, the next step is to append tables ( use append query in the power query ) .
third step :
create a matrix with concept on the columns and person on the rows and aggregation on values .
hope this helps
