Forum Discussion
Display Count in Pie Chart based on Condition
Hello, I have some sample data here:
| ID | Class |
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | A |
| 3 | A |
| 3 | B |
| 4 | A |
| 4 | C |
I'm trying to create a pie chart that displays a count of "Class" but I only want it to display the count where ID has both A & B present. So based on the data above, ID 1 and 3 would contribute to the count because they both have a Class which includes A and B. In the end I'm looking for my Pie Chart to look like this:
Looking for any assistance on how to write this measure properly. Thanks!
In case anyone happens to stumble upon this post with the same question, this is the dax I ended up writing to help me figure out this issue:
Measure =
VAR A = CALCULATETABLE(DISTINCT(Table[ID], Table[Class] = "A")
VAR B = CALCULATETABLE(DISTINCT(Table[ID], Table[Class] = "B")
VAR AB = INTERSECT(A, B)RETURN
CALCULATE(COUNTROWS(Table, Table[ID] IN AB)
And then in the visual tab of the pie chart, put this measure under "Values" and put the Class column from the table under "Legend".
3 Replies
the requirement is confusing ( at least for me).
can you please explain the logic ? like in class A on the pie chart, how do you get the 40% ?
another question related,
if id is in A and B , should i count it in A , and in B ?
what about C class ?best regards
Sorry for the Confusion, Instead of doing percentages, I should of done it by counts instead kind of like this:
So A & B are 2 from ID's 1 and 3 adding to the count since they both have Class A and B in the table.
The reason C shows up is from ID 1, since it meets the reqirement of having both A & B, I also want it to count any other Classes in the chart outside of just A & B, hence why C shows up.
I hope that makes sense!
In case anyone happens to stumble upon this post with the same question, this is the dax I ended up writing to help me figure out this issue:
Measure =
VAR A = CALCULATETABLE(DISTINCT(Table[ID], Table[Class] = "A")
VAR B = CALCULATETABLE(DISTINCT(Table[ID], Table[Class] = "B")
VAR AB = INTERSECT(A, B)RETURN
CALCULATE(COUNTROWS(Table, Table[ID] IN AB)
And then in the visual tab of the pie chart, put this measure under "Values" and put the Class column from the table under "Legend".
