How to separate the salary amount from the range in the same column

[fabiolamelo]

Hi everyone,

I have a table with a number of salaries and a range of it. I am trying to find a way to separate the salary amount from the range in different tables.

This is the table now

City                        IdCountry            Salary and Range             

 Canberra            ACT                        61          

 Canberra            ACT                        56 - 66  

 Sydney                 NSW                      56          

 Sydney                 NSW                      51 - 61  

 Regional              NSW                      48 - 65  

 Regional              NSW                      55          

 Darwin                 NT                          51          

 Darwin                 NT                          46 - 57  

I would like the table to return this

City                        IdCountry            Range of Salary    Salaries

Canberra              ACT                        56 - 66                   61

Sydney                 NSW                      51 - 61                   56

Regional               NSW                      48 - 65                   55

Darwin                  NT                          46 - 57                  51

Is there a way to solve this problem?

Many thanks in advance for your support

Fabiola Melo

[az38 · Accepted solution]

Hi @fabiolamelo 

create a new table

Table 2 = 
ADDCOLUMNS(
    FILTER('Table', SEARCH("-", [Salary and Range],,0) > 0),
    "Salaries",
    CALCULATE(MAX('Table'[Salary and Range]), FILTER('Table', 'Table'[City]=EARLIER([City]) && 'Table'[IdCountry] = EARLIER([IdCountry])))
)

[v-alq-msft · Accepted solution]

Hi, @fabiolamelo 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated table as below.

New Table = 
SUMMARIZE(
    'Table',
    'Table'[City],
    'Table'[IdCountry],
    "Range of Salary",
    CONCATENATEX(
        FILTER(
            DISTINCT('Table'[Salary and Range]),
            CONTAINSSTRINGEXACT('Table'[Salary and Range],"-")
        ),
        'Table'[Salary and Range],","
    ),
    "Salaries",
    CONCATENATEX(
        FILTER(
            DISTINCT('Table'[Salary and Range]),
            NOT(CONTAINSSTRINGEXACT('Table'[Salary and Range],"-"))
        ),
        'Table'[Salary and Range],","
    )
)

Result:

Best Regards

Allan

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

[az38 · Accepted solution]

Hi @fabiolamelo 

create a new table

Table 2 = 
ADDCOLUMNS(
    FILTER('Table', SEARCH("-", [Salary and Range],,0) > 0),
    "Salaries",
    CALCULATE(MAX('Table'[Salary and Range]), FILTER('Table', 'Table'[City]=EARLIER([City]) && 'Table'[IdCountry] = EARLIER([IdCountry])))
)