Data modelling - how to structure cost/sales with details

[DennisSchlein]

Hi 

I'm new to power bi, thus I have completed a few basic courses.

I need advice on how to create my data model - in essence, I have 2 tables, cost and sales.
I work within the transport category, so this is parcel numbers - and surcharges belonging to each parcel number.

These two does not directly have a link, or not everything in my cost table has to be sold.

I want to validate if all parcel numbers we buy, is sold

In my Cost table I can have regular invoices, and credit notes.

And the same goes for Sales.

Right now I generate a LookupValue on my cost table Which is the parcel number from Sales if its knows. i have tried adding the service code as a string ex.: 00370730257845736894_900 and doing the same on my sales table.

If I do that, then I can generate a new mapping table with all the parcelnumbers and service codes represented in both tables.

But on my cost side, I also want  to see cost component with no sales value:

I'm sorry fi this makes no sense to you, but I am open to elaborate if needed.

i have created my data model so far like this:

And thoughts?

[DennisSchlein]

Hi


Thanks - @amitchandak 

It worked - what I ended doing was creating a table with all non profitable parcels.
Then linking them to a summarized table, with all parcelnumbers and "service codes"

And use that to filter both cost and sales.

And so far, so good:



Using this form of data model:



But if I add +0 to my measure:

_SumSalesAmountDKK = SUMX(SalesDetail, SalesDetail[SalesAmountDKK])


Then it act like it looses the relation between my calc table:

_SumSalesAmountDKK = SUMX(SalesDetail, SalesDetail[SalesAmountDKK])+0



Am I drunk?

Thanks in advance, 

[DennisSchlein]

I'll have a look, but that looks great 🙂 

[amitchandak]

@DennisSchlein , You need have additional common dimesions

Invoice NO

Parcel

Lookup value

etc.

Two tables need to be analyzed together with common dimensions and measures

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...