Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
The total amount after adding a dimension: 11345
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.)
"Cus" - only has the customers who chose to upgrad their memberships and the year they did so.
Solved! Go to Solution.
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.
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |