Count based on column

[Anonymous]

Hi All,

I want to count the number of distinct records based on the category column.

This is the calculation I have done 

Measure =

CALCULATE(

DISTINCTCOUNT(Table1[Category]),

ALLEXCEPT(Table1, Table1[Group], Table1[Type])

)

But this is just giving me the calculation of all the distinct category values only

I would like this to displayed be in a card visual:

e.g. Select Category = 2

Premium Category = 2

Basic Category = 4

Group	Type	Category
ABC	GEN	Select
ABC	GEN	Premium
ABC	GEN	Premium
ABC	GEN	Basic
ABC	GEN	Basic
ABC	GEN	Basic
ABC	GEN	Basic
ABC	GEN	Select

Please can someone advise? Thanks in advance

@PowerBI 

[amitchandak · Accepted solution]

@Anonymous , To me this seem count

count(Table1[Category])

There is not distinct value column I can see here, if there is one you can try like

Measure =
Sumx(Values(Table1[Category]), calculate(
DISTINCTCOUNT(Table1[ID]))
)

[Samarth_18 · Accepted solution]

Hi @Anonymous 

As you mentioned you want to display all value in cards. Since you have multiple categories so you have to use multi-row card visual.

Under that visual you can directly drag your category and take a count of it.

PFA screenshot for reference:-

Thanks

[selimovd]

Hey @Anonymous ,

your ALLEXCEPT doesn't make sense.

Try it without and it should work:

Measure = 
CALCULATE(
    DISTINCTCOUNT(Table1[Category])
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[amitchandak · Accepted solution]

@Anonymous , To me this seem count

count(Table1[Category])

There is not distinct value column I can see here, if there is one you can try like

Measure =
Sumx(Values(Table1[Category]), calculate(
DISTINCTCOUNT(Table1[ID]))
)