Forum Discussion

Defraudinho's avatar
Defraudinho
New Member
7 years ago
Solved

Histogram of grouped data

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).

AnimalsId of Zoo
Horse1
Elephant1
Horse1
Horse2
Chicken2
Giraffe2
Horse3
Elephant4
Horse5
Elephant5
Elephant6

 

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

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.