Split column into multiple rows and equally divide a value grouped by the splitted column

Hi Anonymous,

Based on the requirement do the following steps.

1. Load the data.

2. Go to Edit Query of the table

3. Use Split Column to split the column containing City to as many columns as delimited by "," (comma).

    ( Assumption made is Each Country will have three cities separated by comma ).

4. The table will have 3 columns called City.1 City.2 City.3

5. Select the 3 city columns and from Transform Tab of the ribbon Select UnPivot Columns

6. You will then get each Continent, Country, City as individual rows.

7. Remove the column Attribute

8. Now do Group By from Transform column to find the count of rows by Continent, Country.

9. Expand the Table column you get at the end of above group by operation.

10.  Rename the columns

11. Add a custom column Divided Sales = Sales Original / Count Rows

12.  Change the type of Divided Sales as WholeNumber

13. Now plot the Matrix table

You will get the desired output.

This kind of transformation is best done in Power Query than DAX.

If this works for you accept this as Solution and also give KUDOS.

Cheers

CheenuSing