Calculate Category Created in Measure by ID no

[kilala]

Hello all,

I have created a measure to categorise certain condition on my data. The condition is by ID no.

For example;

ID	Amount A	Amount B	Amount C	Category
1	100	0	0	A
2	100	50	0	B
3	100	100	0	B
4	100	100	100	C

I want to display the SUMMARY of count by category with pie chart (if possible). so in my pie chart later, I would have 3 category and value as below:
CAT A -1

CAT B -2

CAT C- 1

how do i do that?

Is it possible/ is there any other alternative i can opt to?

Thanks!

[kilala · Accepted solution]

Hi all,

Thanks for helping.

I think current problem is that the category is measure.

So, I create a table contains all ID and then create conditional column "Category". So, later i just drag the ID and Category together.

It solve my problem as of now, hopefully there wont be any issue later hehe.

[v-rongtiep-msft]

Hi @kilala ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure = CALCULATE(COUNT('Table'[Category]),FILTER(ALL('Table'),'Table'[Category]=SELECTEDVALUE('Table'[Category])))

If I have misunderstood your meaning, please provide your desired output with a screenshot.

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@kilala , You can create pie visual, Drag Categoryto legend, and count to ID to values.

In case you need cat appended you need to create a new column

Cate New =  "CAT " & [Category]

You can also have measure to be used in values

count = count(Table[ID])

[kilala]

Dear @amitchandak ,

When I drag the category, it only shows me 1 category, which is for the total.

I think it is because i create measure? how do i solve this?

[amitchandak]

@kilala , Please find the attached file

[kilala]

Hi @amitchandak ,

from you sample, category is a column in the data table. but as for my case, "category" is a measure, so it doesnt belong to any table. how do i cater this?

[amitchandak]

@kilala , Then you have create a table with these category value , say Cat 

then create a new measure and use that with the Category of this new table

countx(filter(Values(Table[ID]) , [Category] = max(Cat[Category]) ), [ID])