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

[Anonymous]

Hi everyone, 

I would like to split a column with a delimeter into multiple rows and divide the value by the count of rows splitted into. 

Like in a screenshot I attached , I would like to split the cities and then divide sales among them equally

[CheenuSing]

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

[LivioLanzo]

You easily do it like this:

let
    Source = Table.FromRecords( 
                    { [Continent = "Europe", Country = "Germany", City="Munich,Berlin,Cologne", Sales=300] }, 
                    type table [Continent = Text.Type, Country = Text.Type, City = Text.Type, Sales = Number.Type] ),
    
    Trnsfm = Table.TransformColumns( Source, {"City", each Text.Split(_,","), type list}),
    #"Added Custom" = Table.AddColumn(Trnsfm, "Sales2", each [Sales] / List.Count( [City] ), type number ),
    #"Expanded City" = Table.ExpandListColumn(#"Added Custom", "City"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded City",{"Sales"})
in
    #"Removed Columns"

[Anonymous]

Hi, 

I was searching for the DAX on your problem when I discovered this past thread, which is highly relevant to yours. 

https://community.powerbi.com/t5/Desktop/Count-no-of-comma-occurrences-in-a-column/td-p/79183

multiple ways to do it - eg You can delimit the column, but I would create a copy and find DAX to count the number of times a comma occurs.

Sorry I couldn't give a specific query to your issue!