How to count text occurrence of values in a column incrementally and concatenate in a new column

[Anonymous]

Hi Team,

I'm new to PowerBI and experimenting with PowerBI to gain some useful insights into the data that we have.

So far with the Map visualization, I have been able to gain good insights, but it isn't quite helpful when presenting the data.

So what I currently have is a column consisting of the country name where the sale was performed, now what I want to do is it displays the country name and the number of times a sale has been done in that country, now I can see it with tooltip without any issue but isn't something that I can take a screenshot and put it into powerpoint.

What I would like to achieve is to display the number of times the country value appears, so was thinking if there is a way that I can create a new column to count the number of times the value appears in the country column incrementally and then concatenate the country and count and display on the map visualization.

Any help or insights that you would be able to provide is greatly appreciated.

Regards,

Arun

[Greg_Deckler]

It could theoretically be done, just create a column like:

Country&Number = CONCATENATE([Country],COUNTX(FILTER('Countries',[Country]=EARLIER([Country])),[Country]))

But my bet is that is going to screw up Bing maps from geocoding it properly.

[Anonymous]

Hi smoupre,

Thank you for the below, is it possible to add a "-" in between the concatenation or Space as currently what I'm getting is a CountryName1234.

Once I get the above in order I will try to see if I can get the Bing Maps to be going okay.

The Map visualization requires an added functionality of call outs.

Regards,

Arun

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Yes, it is possible. I create sample table. You can create a calculated column using the formula as follows.

CountryName-Number =
CONCATENATE (
    Countries[CountryName] & "-",
    COUNTX (
        FILTER ( Countries, [CountryName] = EARLIER ( Countries[CountryName] ) ),
        [CountryName]
    )
)

Or add space between Country name and count.

CountryName Number = 
CONCATENATE (
    Countries[CountryName] & " ",
    COUNTX (
        FILTER ( Countries, [CountryName] = EARLIER ( Countries[CountryName] ) ),
        [CountryName]
    )
)

Best Regards,
Angelia