Display how many companies have how many categories

[r-ramillien]

Dear powerBI community,

I have a simple table like below:

___________________________________________________________

Company (string)    | Category (string)  | Other column 

___________________________________________________________

Co_A                       | Cat_1                     | A

Co_A                       | Cat_1                     | B

Co_A                       | Cat_2                     | C

Co_A                       | Cat_2                     | D

Co_B                       | Cat_2                     | E

Co_B                       | Cat_3                     | F

Co_B                       | Cat_3                     | G

Co_C                       | Cat_3                     | H

Co_C                       | Cat_2                     | I

Co_C                       | Cat_1                     | J

Co_D                       | Cat_1                     | K

I would like to display a cluster bar and a pie chart which shows how many companies have how many distinct categories.

For example

2 Companies have 2 Categories (because Co_A have Cat_1 and Cat_2 and Co_B have Cat_2 and Cat_3)

1 Company have 3 categories (because Co_D have Cat_1, Cat_2 and Cat_3)

I'm sure its pretty straightfoward for many of you, but I can't find a way to do it. I tried to create few custom measures, but I can't add them to the "legend" or "axis" of the chart 😞

Thank you for your help ! 🙂

[Greg_Deckler · Accepted solution]

@r-ramillien One way, create an Axis table: 

Axis = GENERATESERIES(1,100,1)

The a measure: 

Measure =

VAR __AxisValue = MAX('Axis'[Value])

VAR __Table = SUMMARIZE('Table',[Company],"__Cat",COUNTROWS(DISTINCT('Table'[Category])))

RETURN

COUNTROWS(FILTER(__Table,[__Cat] = __AxisValue))

PBIX file is attached below sig.

[r_ramillien]

Hello,

Thank you, it work great !
I still have to understand how it works, but it works 🙂

Sorry, I cannot accept the answer right now, there is a problem with my account bound with the one of microsoft 365...

Best regards

[amitchandak]

@r-ramillien , to me it seems like

distinctcount(Table[Category]) with Company as legend in pie

[r_ramillien]

Thank you for your response,

You're right about the distinct count, but my problem is more with the legend.

I need to have the number of companies with a specific number of categories, not the name of the company itself.

Best regards