Wrong totals after unpivoting

[banjofork]

Hello BI gods,

I am currently working on an allocation report. I had to unpivot 4 colums into one and now i'm stuck with some wrong totals.

My goal is to calculate the number of categories by tasks (the ratio between task to category is 1 to 1, meaning there can be no more than 1 category in a task and vice versa), distinctcount doesn't work because unlike tasks, the list of categories is around 10 and tasks are around 100+ and after unpivoting there are a lot of duplicates.

To vizualize my problem. I want to know how many categories

Task                                   category

Potato	Orange
Potato	Orange
Potato	Orange
Potato	Orange
Tomato	Green
Tomato	Green
Tomato	Green
Strawberry	Red
Coconut	Orange
Coconut	Orange
Coconut	Orange
Coconut	Orange
Apple	black
Apple	black
Apple	black
Apple	black

The results i'm looking for is: 2 orange, 1 green, 1 red, 1 blue, 1 blacketc.

P.S. HELP 

[v-yuaj-msft]

Hi @banjofork ,

Sorry for that the information you have provided is not making the problem clear to me.

Can you share some sample data and the expected result to have a clear understanding of your question?

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

Best Ragards,

Yuna

[banjofork]

21

Righto, what i basically want is that my pie chart shows the correct amount of categories fx. 

If the selected person has 3 tasks there should be only 3 categories ( 1 to 1 ratio for title and category). Currently it counts the number based on how many times a title is being iterated...

EDIT: For some weird reason it looks like the categoryid and category.title mismatch on some entries, but that is not the case, i simply did too many edits of the screenshot.

[ryan_mayu]

@banjofork 

have you tried split the column to two and create a measure?

Measure = CALCULATE(DISTINCTCOUNT('Table'[Task]),ALLEXCEPT('Table','Table'[category]))

[banjofork]

I've created an id table for the categories, but your suggestion did not work for me. What I'm trying to do is visualize in a pie chart the ratio of categories from the total of tasks.

[ryan_mayu]

@banjofork 

Could you please share the sample data and output in excel? screenshot is also fine.