calculations on a field gives me headache: is my data model ok?

[tvanover]

Hello,

I have a report where I need to do some calculations on qty ordered, qty delivered and current stocklevels.

The problem is that one of the fields 'stocklevel.qty on stock', is giving me headaches. I have simplified my data model  and kept only the relevant tables to show the problem, it can be download here: https://www.dropbox.com/s/tt08pbmag5wykvv/test-stocklevel.pbix?dl=0

In the report you see that I filtered on 1 specific order: I have orderlines, associated articles, and qty ordered and delivered.

I also have a column 'qyt on stock' that comes from a table 'stocklevel' that is linked to my 'articles' table.

Now I am trying to do basic calculations, e.g. ordered qty - delivered qty. I created a measure for it 'ordered qty minus delivered qty'. The results are as expected.

The column 'Qty on stock' also displays correct values.

However, as soon as I try to create a measure that involves 'qty on stock', everything seems to fall apart. (you can see that by enabling the measure 'qty on stock minus ordered qty'. If I add this measure, it affects the values of the other columns, and I don't understand why.

Could someone have a look, and explain how I can obtain for example via a measure: 'ordered qty - delivered qty - qty on stock'?

I'd be ever so gratul.

Thanks,

tom

[tvanover]

Hello,

1) I can only make a bi-directional relationship between these 2 tables: if I try to make it one-direction, I get receive the message 'the filter direction you selected isn't valid for this relationship

2) I am using the article number field. Also, the stocklevels report fine when I add them to the visual.

It's just that I don't know how to use them in a calculation correctly.

I also noticed that I provided the wrong screenshot for the datamodel. Here's the correct one:

[v-kelly-msft · Accepted solution]

Hi  @tvanover ,

Change the direction between ‘SalesOrderLines’ and ‘GoodsDeliveryLines’ and ‘SalesOrderLines’ and 'Articles' from single to both,as shown below:

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mahoneypat]

Two things probably related to your issue.

1. Your sample file has a bidirectional relationship between the Articles and stocklevel tables.

2. You are not using the ArticleNumber column from the Articles table, so it is not filtering the StockLevels table (with single or bi-di relationship).

Regards,

Pat 

[tvanover]

Just to make it easier to understand, I'll add a screenshot of the data model, and of the report.