Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
Two simple tables:
1) Sales: with Sales number with
2) SoldItems: with the pieces sold with
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
Solved! Go to 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 , 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]))
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 , 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
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |