Group data by country

[mrclay82]

Hi,

I have some sales data per country that I want to show.

But for some countries, I want to group the result.

So if the country is Belgium, Netherlands or Luxembourg the result should show as “Benelux”.

The only requirement is that I need to create a measure, and not a calculated column to achieve this.

From this:            

Country               Sales amount

Germany               5

Belgium                3

Luxembourg         2

Netherlands          3

To this:

Country               Sales amount

Germany               5

Benelux                 8

Thanks!

[tamerj1]

Hi @mrclay82 
You can creat a new calculated column (Country New) with IF statment Like

IF ( 'Table'[Country] IN { "Belgium", "Netherlands", "Luxembourg" }, "Benelux", 'Table'[Country] )
Then use the new Country column in the visual.

[mrclay82]

Hey @tamerj1 !
You have helped me before 🙂
Unfortunatelly I'm restricted to only use a measure to solve this, not a calculated column.
Could you please advise? 🙂

Br

[tamerj1]

@mrclay82 
Yes, but you still can locally create a a disconnected table (Countries) that contains all countries but replacing "Belgium", "Netherlands" and "Luxembourg" with “Benelux”. Use this coulm in the table visual along with the following measure

=
SUMX (
    VALUES ( Countries[Country] ),
    IF (
        Countries[Country] = "Benelux",
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            'Table'[Country] IN { "Belgium", "Netherlands", "Luxembourg" }
        ),
        CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[Country] = Countries[Country] )
    )
)

[mrclay82]

No I can't:

I need to solve this with a measure only somehow.

[tamerj1]

@mrclay82 
Then the only solution is ask the data owner to add the column for you.

[mrclay82]

Aren't there any table manipulating function I can use to nest in a measure?

[tamerj1]

@mrclay82 
Of course there are. But that won't get you anywhere. You need a column to slice by in a ny visual. You cannot slice by a measure. This is how Power Bi functions.

[some_bih]

Hi @mrclay82 your measure should be Sales = SUM(YourTableName[Sales amount])

YourTableName - is simply your table name. I hope this help