Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Split the combined value into two separate lines

Hello Community,  How can I transform the table: ITEM_LIST ID VALUE XYZ 1 700 ABC | XYZ 2 1000 ABC | QWE | ASD 3 600 Where in DESCRIPTION  list of items with delimeter " | "...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, please take following steps:

    1)Split [ITEM_LIST] into rows by " | ":

    2)After Apply&Close, create a measure as below:

    Measure = SUM('Table'[VALUE]) / CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),'Table'[ID] in FILTERS('Table'[ID])))

    3)The result shows:

    Here is my test pbix file:

    pbix 

    Hope this can help.

     

    Best Regards,

    Giotto Zhi

     

v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi,

 

According to your description, please take following steps:

1)Split [ITEM_LIST] into rows by " | ":

2)After Apply&Close, create a measure as below:

Measure = SUM('Table'[VALUE]) / CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),'Table'[ID] in FILTERS('Table'[ID])))

3)The result shows:

Here is my test pbix file:

pbix 

Hope this can help.

 

Best Regards,

Giotto Zhi