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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate average price per item over two tables with iteration

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:

1.png

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

2.png

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 

 

1 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

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@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
Not applicable

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

I uploaded two pictures that might help.

 

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
Not applicable

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

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

 

@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
Not applicable

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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