Sum of value does not match with the original source sheet

[fksng66]

I'm trying to create a bar chart for impression on instagram but the total number of impressions are not tally with the original sheet. 

On my original sheet it showed the total sum of impression for NSEA territory on Jan alone was 8162, but on my power bi chart it displayed 8.8k. same thing for the subsequent months too

i tried to filter it on data view, it matches the sum value from original source sheet and it was able to show the correct value

  

sum value from original source sheet

but once i removed the filter on data view and it's back to original. i can't really figure out what went wrong.

would appreaciate if anyone would help me figure out this or point out if i missed out anything. thanks!

[fksng66 · Accepted solution]

okay it worked now, i've the territory (paidAd_fb) connected to territory (facebook) to form a many-to-many relationship and it's able to show the correct value for both organic and paid ads now

[Anonymous]

Hi  @fksng66 , 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution or mark the helpful reply, it will be helpful for other members of the community who have similar problems as yours to solve it faster . Thank you very much for your kind cooperation!

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[HotChilli]

Thank you for the pbix - it's always the best way to get help.

As i was trying to show you in my original response, the data labels on each bar are in the wrong format for proper reconciliation.

75447 displays as 75K

14955 displays as 15K

Under the General tab of the Format Visual is where you change that for the column chart.

--

For a measure in a card, it can get complicated but under Callout Value there is a Display Units property

---

The other filtering issue is caused by not having one or more common dimension tables to filter your Fact tables (with relationships). I can't get into that here, it's all available here:

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema 

[HotChilli]

The first thing you need to do is get the correct datatypes on the columns in your data. 'abc123' is the 'any' datatype and it should be used in only exceptional circumstances.

The next thing to do is get a more accurate number on your data columns in the chart. '8.8K' is the wrong format to reconcile numbers.

So get on with that please and we can investigate further.

[fksng66]

I thought I've the datatype changed to whole number under the table view already. but i've changed it to whole number datatype under the edit query.

but the still not showing the right value 😕

[Anonymous]

Thanks for the reply from HotChilli.

Hi  @fksng66 , 

I created simple data to test this, and filtering on month or not has no effect on the impression value:

 

I think the result of this error has something to do with your current context or relationship between tables. If you can provide me with a sample PBIX file after removing sensitive data that contains your current error-displaying diagram, then it would be very helpful for me to investigate it.

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[fksng66]

hi @Anonymous  thanks for the reply,

i couldn't seem to upload the file here so i uploaded it into google drive.

https://drive.google.com/file/d/1k5G4Xl4gCAnmcuO4MY9M4VWbAo1N2fj9/view?usp=drive_link 

i've striped it down to only include the facebook and paid ads facebook sheets.

another question i've is how do i be able to apply the filter to filter tha data for all the charts? right now when i filter it only effects one of the charts, while the other remained same

caused if i do the filtering on the query editor then i was able to get the data i want (eg territory:NSEA, month: Jan), so i figure the issue im having is on the filter side instead

PS: correct impressions for Jan month, NSEA should be 90402