Calculated column taking a count fixed to a country column

[KristofferAJ]

I would like to make a column or measure which takes my country column and return a number of houses in a certain colour.

For instance I have 23 yellow houses in my dataset in US and 14 yellow houses in Poland

A: Country column (e.g. Poland, United States)

B: Colour of house (e.g. Red, Yellow)

My wish i to get this out:

COUNTRY (Count of yellow houses)

United States (23)

Poland (14)

In tableau I would use a count and a FIX logic, i wonder if powerBI is able to make counts fixed to a specific country i a column

[jaideepnema]

@KristofferAJ ,

If i understood correctly have you tried creating a table or any visual and apply a visual level filter of color=yellow ?

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[Fowmy]

@KristofferAJ 

Insert a table visual and add the country field, then create a new measure as follows and addit:

No of Houses = COUNTA ( TABLE[Colour of house])

If you need explicit measures to use in a card visual then create two different measures:

No of Houses  USA = CALCULATE ( COUNTA ( TABLE[Colour of house]) , TABLE[Country] = "United States" )

No of Houses  Poland = CALCULATE ( COUNTA ( TABLE[Colour of house]) , TABLE[Country] = "Poland" )

[KristofferAJ]

Thank for your quick response, however I need this in one column only:

Poland (14)
United States (23)

so the fomula needs to takes the country in from the country column A, and then count all the houses with Yellow from column B

[Fowmy]

@KristofferAJ 

Try this one please:

No of Houses  USA = CALCULATE ( COUNTA ( TABLE[Colour of house]) , TABLE[Country] = "United States" , TABLE[Colour of house] = "Yellow" )

No of Houses  Poland = CALCULATE ( COUNTA ( TABLE[Colour of house]) , TABLE[Country] = "Poland", TABLE[Colour of house] = "Yellow"  )

[KristofferAJ]

Thanks, but I have 43 countries, and new coming in, so I would need a formula which picks it up from the country column and not by find "United States"

[Fowmy]

@KristofferAJ 

If you need it to be dynamics then use the following measure in a matrix, you will have Country in Column and Color in Row section and the measure in the value section, the calculation will work correctly following the filter context at each intersection of country and color.

No of Houses = COUNTA ( TABLE[Colour of house])

[KristofferAJ]

Hi again, 

Still trying to get this to work.. 

One piece is clear (being the counts) - however I can get it to work when combining it with the name of the country.

Issue is that the numbers will change depending on filters and data input

For the count i used a measure, and for the country (Repsonses) i used a column