topic Re: Display how many companies have how many categories in DAX Commands and Tips

Display how many companies have how many categories

r-ramillien — Thu, 29 Apr 2021 11:39:22 GMT

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 ! 🙂

Re: Display how many companies have how many categories

amitchandak — Thu, 29 Apr 2021 12:12:09 GMT

r-ramillien , to me it seems like

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

Re: Display how many companies have how many categories

Greg_Deckler — Thu, 29 Apr 2021 12:17:51 GMT

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.

Re: Display how many companies have how many categories

r_ramillien — Fri, 30 Apr 2021 19:03:12 GMT

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

Re: Display how many companies have how many categories

r_ramillien — Fri, 30 Apr 2021 19:05:19 GMT

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