cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
liaplia
Helper I
Helper I

Count inconsistency in graphic

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:

PaintingIDPaintingName
1Painting 1
2Painting 2
3Painting 3
4Painting 4
5Painting 5

 

Table 2: (this table links together paintings and colors in the painting)

PaintingIDColorID
11.1
12.1
12.2
21.1
21.2
22.1
31.2
31.3
42.1
42.2
41.3
51.1
52.2

 

Table 3

ColorIDColorNameCategoryID
1.1Blue1
1.2Green1
1.3Purple1
2.1Red2
2.2Orange2

 

Table 4:

CategoryIDCategoryName
1Cool Colors
2Warm 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 RedCount of OrangeCount of Warm Colors
334

 

 

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

7 REPLIES 7
v-stephen-msft
Community Support
Community Support

Hi @liaplia ,

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!

HotChilli
Super User II
Super User II

In general, this is a measure total problem, however, I put the bar chart together and I get this:

Screenshot 2020-12-14 171823.png

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:

Screenshot 2020-12-14 172130.png

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 @liaplia ,

 

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.

31.png

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors