Forum Discussion
Table structure
Hi,
I'm working with data that will have about 10 million rows per day. Currently, the rows are nested which is something Power BI can't handle. The question that arises is the structure of the table. We have sessions where each sessions has multiple moments. We are trying to avoid the increase of rows, if we have 5 moments, then each session will have 5 rows with one type of moment per row. I know Power BI wants to work with rows and columns in a tabular format, but is there an optimal way to have for example one moment per column and at the same time have one session per row? Tables to try to explain it:
Session ID Moment A Moment B Moment C
| 123A | 30 | 50 | 20 |
| 231B | 10 | 30 | 20 |
| 321C | 90 | 40 | 50 |
Session ID Moment Time
| 123A | A | 30 |
| 123A | B | 50 |
| 123A | C | 20 |
| 231B | A | 10 |
| 231B | B | 30 |
| 231B | C | 20 |
| 321C | A | 90 |
| 321C | B | 40 |
| 321C | C | 50 |
We want to be able to show the percentage of the total, so for session ID 123A Moment A, it's 30% of the total etc.
Thanks and Merry Christmas!
/Jari
2 Replies
Anonymous , the second one is ideal for you need but if you have 10 Million per day. Unpivot of this data will take huge time and may not work.
In that case if you have limited columns you can try like
divide(sum(Table[ColumnA]), sum(Table[ColumnA])+sum(Table[ColumnB])+sum(Table[ColumnC])+sum(Table[ColumnD])+sum(Table[ColumnE]))
