column split by delimiter value lightly different

[Anonymous]

 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 .

[v-kkf-msft · Accepted solution]

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,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft · Accepted solution]

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,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

its wokring . i have multiple value column so i want add index column for all  or else i can i use this index for all column .some column values still not matching 

[lbendlin]

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.