Forum Discussion
column split by delimiter value lightly different
i have table with comma values column like
| Adjusted | Density | Factory | OD |
| 205.6 | 8.4 | 23 | PL04 |
| 212.4 | 1.6 | 23 | SA04 |
| 204.1 | 2.9 | 23 | DE04 |
| 204.3 | 0.7 | 23 | DE04 |
| 210 | 4 | 23 | SA04 |
| 198.4 | 7.6 | 23 | PL04 |
| 204.3 | 0.7 | 23 | DE04 |
| 203.5 | 0.5 | 23 | FR04,FR05 |
| 206.5 | -0.5 | 23 | HR04 |
average value avg :77.111 now splited column comma value by delimiter like
| 205.6 | 8.4 | 23 | PL04 |
| 212.4 | 1.6 | 23 | SA04 |
| 204.1 | 2.9 | 23 | DE04 |
| 204.3 | 0.7 | 23 | DE04 |
| 210 | 4 | 23 | SA04 |
| 198.4 | 7.6 | 23 | PL04 |
| 204.3 | 0.7 | 23 | DE04 |
| 203.5 | 0.5 | 23 | FR04 |
| 203.5 | 0.5 | 23 | FR05 |
| 206.5 | -0.5 | 23 | HR04 |
this average lightly different 76.08 . any idea i am only did splited by column in to row . any idea .
Hi Anonymous ,
If you want the average after splitting to be equal to the average before splitting, you need to add the index column, and then split the column, like this.
The purpose is to divide the split rows into a group, and only calculate the data of one row when calculating the average value. Using the following formula to calculate the average will get the same result as before splitting the column.
Measure = DIVIDE( SUMX( SUMMARIZE( 'Table', 'Table'[Index], "One_Adjusted", MAX('Table'[Adjusted ]) ), [One_Adjusted] ), DISTINCTCOUNT('Table'[Index]) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
you have one more row in your table after the split of FR04,FR05 into one row for FR04 and one for FR05.
So however you calculate the average (not clear from your data) you're now dividing by 10 rather than 9.
Hi Anonymous ,
If you want the average after splitting to be equal to the average before splitting, you need to add the index column, and then split the column, like this.
The purpose is to divide the split rows into a group, and only calculate the data of one row when calculating the average value. Using the following formula to calculate the average will get the same result as before splitting the column.
Measure = DIVIDE( SUMX( SUMMARIZE( 'Table', 'Table'[Index], "One_Adjusted", MAX('Table'[Adjusted ]) ), [One_Adjusted] ), DISTINCTCOUNT('Table'[Index]) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
