Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anik
Helper I
Helper I

Wrong calculation

Capture.PNG Here I've encountered a problem. In 2013, the number of sales was 12,760. When I dragged the (country) column into the Columns section, an additional blank row is created. Whatever columns (country/city/region etc.) I dragged into the matrix  columns section, the problem was same.  

2 ACCEPTED SOLUTIONS
Arentir
Resolver III
Resolver III

Hi @Anik ,

Your Date dimension relates to the delivery date key field in Fact table, and you do have records with blank delivery date in the fact table. This is why the dimdate field is empty, and power bi won't filter them out by default in case you need them!

 

If you want to avoid that, you could add a filter on the visual/page/report to exclude those cases.

 

image.png

 

 

View solution in original post

@Anik Try using Invoice Date Key instead to form your relationship. You have blanks in your Delivery Date Key.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Arentir
Resolver III
Resolver III

Hi @Anik ,

Your Date dimension relates to the delivery date key field in Fact table, and you do have records with blank delivery date in the fact table. This is why the dimdate field is empty, and power bi won't filter them out by default in case you need them!

 

If you want to avoid that, you could add a filter on the visual/page/report to exclude those cases.

 

image.png

 

 

Anik
Helper I
Helper I

Capture.PNGCapture 2.PNG

Hi @Anonymous and @Greg_Deckler , the left image was taken from a book, the right one was done by me. I tried and inspected everything according to my knowledge. I am sure I cannot hammer on the right place due to my inexperienced eyes. I have attached the practice file. Could you please go it over and tell me what's the mistake I made so that I don't make the same mistake over and over again?  Thank you 😊  https://drive.google.com/file/d/1qFkzdEYZw3PffdZz3yngv5xluowB4TF5/view?usp=sharing     

@Anik Try using Invoice Date Key instead to form your relationship. You have blanks in your Delivery Date Key.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler , @Arentir , thank you so much 😊. Next time I am going to keep in mind about the blank cells while making relationships.  

Hi,

In the Delivery Date key column of the Fact sales table, there are some blank cells.  Plug them in with Dates.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@Anik There is likely something going wrong in a relationship between two tables. You tend to get blanks when rows don't match up. For example:

 

Year

2013

2014

2015

 

Fact

Year   Value

2012  2

2013  5

2014  6

2015  2

 

Assuming a relationship between the two tables on Year if you used Year from your Year table and a sum of Value from your other table you would get a blank row that is essentially the 2012 data.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

This is very basic, but have you checked the Calendar YEar column for those 55 entries to ensure they are formatted as date? (I assume yes, as I am assuming from your post that when no additional column is added in, the report correctly shows 12,760?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.