Calculate average price per item over two tables with iteration

[Anonymous]

Hello

Two simple tables:

1) Sales: with Sales number with

• Category (Category 1 - Category 3)
• Years (2016-2019)
• Workstream (WS1 -WS6)

2) SoldItems: with the pieces sold with

• Category (Category 1 - Category 3)
• Years (2016-2019)
• Workstream (WS1 -WS6)

Obviously, tables have the same lenght.

The tables are connected three dimension tables for Years, Category, Workstream.

I want to have a measure that calculates Average Sales per Item (Sales/Solditem).

Assume I don't want to merge with Power Query, I rather use DAX Measures.

I know that something like this is wrong, since it does not consider iteration:

Z_Sales_p_Item = 
var A = SUM(tbl_SalesMargin[Sales])
var B = sum(tbl_SoldItems[SoldItems])
return DIVIDE(A;B)

It hast to calculate correctly even if my table does not show for example "Workstream", only Years and Category.

Is there a solution?

Here are pictures upload:

1) If I use the formula above (lhs) and show the whole table, it is fine:

2) If I kick out Workstream, then the measure on the lhs becomes wrong:

where the rhs shows the correct Averages when using "merge" in Power Query and using some AverargeX formula.

Here is the file:

https://1drv.ms/u/s!AlL0Z-uEr7s2ky16eAnjl6wblp2S?e=bNjo8C 

[amitchandak]

@Anonymous , these formulae, and structure seems fine to me, what is the problem you facing if you can share an example. The only one change you can try is

Z_Sales_p_Item =
DIVIDE(SUM(tbl_SalesMargin[Sales]);sum(tbl_SoldItems[SoldItems]))

[Anonymous]

Thanks, @amitchandak ,but no. That is the same formula as I had written, only differently.

I uploaded two pictures that might help.

[amitchandak]

What I got is that merged table is giving correct result and two tables with common separate dimension is not.

Try this

Z_Sales_p_Item = 
var A = SUM(tbl_SalesMargin[Sales])+0
var B = sum(tbl_SoldItems[SoldItems])+0
return DIVIDE(A;B)

If possible,Can you share sample data.

[Anonymous]

Thanks again, @amitchandak , but that does not work.

I left a link to the file in the original post.

[amitchandak · Accepted solution]

@Anonymous , According to me what you are calling wrong is right.

Divide should always be sum(A)/Sum(B). They one you are calling right is doing Avg(A/B). Which is simple Avg of row.

Anyways I create a new formula to do that.

The file is attached after signature

[Anonymous]

Your newly created formula is exactly what I sought for, thank you @amitchandak .

You may want to verify it in excel, the simple Sum(A)/Sum(B) is adding all the sales first, and divides it with all the added items. It does not consider each sales_per_item, which is different, and then calculating the average. Now, with your formula, it iterates correctly through the table, even if a non-value field is omitted when displaying.