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

Inconsistent Display in Stacked Column Chart

Dear all

 

While dragging data from two seperated charts to make a stacked column chart, the total amount is different from the number added up to total after adding a dimension. 

 

The origional total amount: 11032

 

 

without_filter.JPG 

 

The total amount after adding a dimension: 11345

 

NA_filtered.JPGOthers_filtered.JPG

 

The relationship between those two tables are

 

Address ----> Many to One - Both <----- Cus

 

Those two tables are:

 

"Address" - contain the most-up-to-date address record for each customer. So, this table contains more data since whether those customers chose  to upgrade their membership or not, their customer IDs will be  in this chart. 

(I added a new group in Power BI: place USA & CANADA to "NA", and the rest countries are in "Others" bucket.)  

 

Address.JPG 

 

 

"Cus" - only has the customers who chose to upgrad their memberships and the year they did so.

 

Cus_Year.JPG  

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Inconsistent Display in Stacked Column Chart

Hi Ryan,

 

If Address table can have more then one records against one customer, this is the intended behavior.

 

A customer having one address of USA and one of JAPAN will be count (distinctively) as one when you are not splitting the count by country_code. When you add country_code dimension, same customer will be counted in both 'NA' and 'Others'.

 

Solution? Try selecting "Count" instead of "Count (Distinct)" in Value Field where you dropped Customer_ID. Or you will have to keep only one (latest) record per customer in address table.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Re: Inconsistent Display in Stacked Column Chart

Hi Ryan,

 

If Address table can have more then one records against one customer, this is the intended behavior.

 

A customer having one address of USA and one of JAPAN will be count (distinctively) as one when you are not splitting the count by country_code. When you add country_code dimension, same customer will be counted in both 'NA' and 'Others'.

 

Solution? Try selecting "Count" instead of "Count (Distinct)" in Value Field where you dropped Customer_ID. Or you will have to keep only one (latest) record per customer in address table.

View solution in original post

ryan1982 Helper I
Helper I

Re: Inconsistent Display in Stacked Column Chart

Thanks a lot. I found some customers had more than one addresses in the records (a.k.a "Address" table) either due to move, human error, or other reasons. After removing those duplicate addresses and making one ID only had one corresponding address, those two charts matched. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors