Unable to use simple formula due to table relationships (probably)

[Anonymous]

Dear PowerBi Guru's,

I have two tables:

1st table represents an order list, where all the orders are organized by date.

2nd table represents additional costs per date per client.

(invoice in the first table is not the same as invoice in the second table)

I have a formula that calculates an average price for one product per kg. And I want to add to that an average additional price for the date.

But when I try do it DAX somehow calculates it for every date of that client (despite the fact that in the second table I have a date which should corrsepond to the date in the first table).

Could you please help me solve this seemingly simple problem?

[BA_Pete]

Hi @Anonymous ,

I'm assuming that the additional costs occur for the client on the same date as the shipment date. If so, then I'd probably merge the tables in Power Query to get everything together there:

In Power Query, select your Orders table then go to Merge on the ribbon.

Select your Additional Costs table as the second table in the merge.

Hold down Ctrl button, and select the [Client] and [Date of Shipment] fields (you should see a little 1 & 2 appear on those fields).

Hold down Ctrl button again and select [Client] and [Date] fields from you second table.

Once merged, expand the [Price] field to add this to your Orders table for each Client-Date.

You should now be a able to use this value in standard measures/calculations.

Pete

[Anonymous]

@BA_Pete oh I got it, thanks a lot!

[BA_Pete]

Hi @Anonymous ,

Did that work out ok for you?

Is so, please mark the answer that worked for you as the solution so others can find it quicker if they need help.

Pete

[Anonymous]

@BA_Pete  Not quite.Turns out  I have three invoices with the same name and the price from the second table triples due to that 😞

[BA_Pete]

@Anonymous ,

I assume you mean that your 1st table is not distinct i.e. you can have repeating order/invoice numbers but with different numerical values?

If so, then you should be able to group this table to remove duplicates. Follow these steps on your first table BEFORE the steps where you merge the second table:

1) In your first table, Ctrl+click all your dimension columns i.e. any column that is not a numerical value.

2) On the Home tab, find Group on the ribbon and select.

3) Should should see all your dimension columns are already selected in the Group By list. In the Aggregate section underneath this, select each of your numerical columns one by one, name them, and select Sum as the aggregation type.

This should hopefully leave you with just one row per order/invoice which will avoid the problem of muliplying merged values.

Pete

[Anonymous]

@BA_Pete  It looks like it shows a right row from the second table at the end of the orders row. However, I am not able to use any numbers that were from the second table to calculate. I am assuming that I missed the "Once merged, expand the [Price] field to add this to your Orders table for each Client-Date." point, can you explain how to do this please?

[amitchandak]

@Anonymous , 'additional cost'[Date] is at wrong place. Can share formula in text format. 

Can you share sample data and sample output in table format?