Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 " | ". To another table with condition EST_VALUE = VALUE / N, where N - count of different item in description:
ITEM | ID | EST_VALUE |
XYZ | 1 | 700 |
ABC | 2 | 500 |
XYZ | 2 | 500 |
ABC | 3 | 200 |
QWE | 3 | 200 |
ASD | 3 | 200 |
Solved! Go to Solution.
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:
Hope this can help.
Best Regards,
Giotto Zhi
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:
Hope this can help.
Best Regards,
Giotto Zhi
Please refer : Power query Split by delimiter into rows.
https://community.powerbi.com/t5/Desktop/How-to-split-the-the-Column-into-Multiple-rows/td-p/253361
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |