Histogram of grouped data

[Defraudinho]

Hello,

I don't know how to resolve this problem.

Imagine I have a list of Zoos and animals living in it (list is thousands of rows long).

Animals	Id of Zoo
Horse	1
Elephant	1
Horse	1
Horse	2
Chicken	2
Giraffe	2
Horse	3
Elephant	4
Horse	5
Elephant	5
Elephant	6

I would like to create a histogram with every "kind" of zoo. In this example:

We have

1 zoo with chickens, horses and giraffes

2 zoos with horses and elephants

2 zoos with elephants

1 zoo with horses

I counted it now, but how to get it in PBI?

I don't care if in the first zoo there is more than one horse

Regards

[v-yuta-msft · Accepted solution]

@Defraudinho,

Firstly create a calculate colunm to mark every row:

Flag = 
VAR Type1 = CALCULATE(DISTINCTCOUNT('Table'[Animals]), FILTER(ALLEXCEPT('Table', 'Table'[Id of Zoo]), 'Table'[Animals] in {"Chicken", "Horse", "Giraffe"})) = 3
VAR Type2 = CALCULATE(DISTINCTCOUNT('Table'[Animals]), FILTER(ALLEXCEPT('Table', 'Table'[Id of Zoo]), 'Table'[Animals] in {"Horse", "Elephant"})) = 2
VAR Type3 = CALCULATE(DISTINCTCOUNT('Table'[Animals]), FILTER(ALLEXCEPT('Table', 'Table'[Id of Zoo]), 'Table'[Animals] in {"Elephant"})) = 1
VAR Type4 = CALCULATE(DISTINCTCOUNT('Table'[Animals]), FILTER(ALLEXCEPT('Table', 'Table'[Id of Zoo]), 'Table'[Animals] in {"Horse"})) = 1
RETURN
SWITCH(TRUE(), Type1, "zoo with chickens, horses and giraffes", Type2, "zoos with horses and elephants", Type3, "zoos with elephants", Type4, "zoo with horses")

 

Then drag Flag and Id or Zoo to the stack column chart like below:

 

Community Support Team _ Jimmy Tao

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