Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
This Question has been better presented in a new forum post:
Count inconsistency in graphic - Clarified Questio... - Microsoft Power BI Community
Please go there if you are interested in this discussion as the discussion here is incomplete
I am very new to BI and data analysis in general. I searched the forums for topics addressing this specific question, but I couldn’t find anything. I apologise if I missed an existing thread. I understand that the database structure I am working with is not that great, but I cannot edit the database.
My post seems to not be visable in the forum - I cannot see it in the topic list -so I am trying again - apologies if it suddenly shows up twice.
A discription of the database is below, followed by my question in bold.
Let’s say I am working with a database that records a collection of paintings and the colors found in each. There are 4 tables:
Table 1:
PaintingID | PaintingName |
1 | Painting 1 |
2 | Painting 2 |
3 | Painting 3 |
4 | Painting 4 |
5 | Painting 5 |
Table 2: (this table links together paintings and colors in the painting)
PaintingID | ColorID |
1 | 1.1 |
1 | 2.1 |
1 | 2.2 |
2 | 1.1 |
2 | 1.2 |
2 | 2.1 |
3 | 1.2 |
3 | 1.3 |
4 | 2.1 |
4 | 2.2 |
4 | 1.3 |
5 | 1.1 |
5 | 2.2 |
Table 3
ColorID | ColorName | CategoryID |
1.1 | Blue | 1 |
1.2 | Green | 1 |
1.3 | Purple | 1 |
2.1 | Red | 2 |
2.2 | Orange | 2 |
Table 4:
CategoryID | CategoryName |
1 | Cool Colors |
2 | Warm Colors |
The problem:
I have two figures.
One is working as I would like. It is a doughnut chart with Legend: ColorName and Values: count of PaintingID
With it I can see the most prevalent color used in the painting collection.
The purpose of the second figure that I am having an issue with is to see which category is more prevalent. I created a bar chart with Axis: CategoryName, Legend: ColorName, and Value Count of PaintingID.
The problem is that the program is counting ColorName and CategoryName differently.
Taking the example of CategoryName = Warm Colors, the chart is trying to display this:
Count of Red | Count of Orange | Count of Warm Colors |
3 | 3 | 4 |
I would like count of Warm Colors to be 6.
How can I fix this? I do not see a way to ask the program to not do a distinct count on CategoryName. Or if there if another display format/chart that would accomplish what I am trying to veiw better, that sounds good too.
TIA
Hi @Anonymous ,
Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Stephen Tao
I have reframed the question in a new entry:
Count inconsistency in graphic - Clarified Questio... - Microsoft Power BI Community
I edited the beginning of this post to hopefully redirect readers from this thread.
Best,
Thank you for your response. I just returned from winter holidays and will be returning to this task tomorrow. I will look at the offered solution in detail then and respond. Happy New Year!
In general, this is a measure total problem, however, I put the bar chart together and I get this:
so my question is "where do you see the total in the version you have?"
----
If I put the same visual as a matrix, I get this:
which does show the totals.
--------------
If you want, you can link your pbix from a 3rd party site and I'll have a look.
Attempt to post reply part 1 of many:
Thank you for taking a look at my question. Yesterday I constructed the dummy database I gave in my first post and plugged that into PowerBI, and like you I didn't encounter my issue. Apparently some of the columns in the real version of table 3 and 4 are more important than I realised. The expanded tables 3 and 4 are below:
Hi @Anonymous ,
I think you may want the following visual. Put the Category Name in Filters and check Warm Colors. Then change the name of the Total value to Count of Warm Colors.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
this is a test message since the boards are not allowing me to post my reply
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |