topic Re: Spread whit DAX in DAX Commands and Tips

Spread whit DAX

Simon_Br — Wed, 13 Dec 2023 07:58:37 GMT

Hello,

i have wants again a question to the DAX-Specialist. I have a table whit Product and Cost. Furthermore Cost whitout Product but the same UniqueDutyNumber. I will now spread the Cost whitout Product on the Product whit the same UniqueDutyNumber. See Example.

We can i to that in DAX?

UniqueDutyNumber	OrderNo	Order-Nr.	Import	Export	Solution
343299	Cola	43111	0	102	47%	29.4075271
343299	Fanta	43488	0	114.55	53%	33.0258062
343299			62.43333333
387889	Beer	43626	59.7	192.8666667	57%	57.1446234
387889	Wine	874456	1.683333333	135.8833333	40%	40.2609847
387889	Water	99329	100.25	9.6	3%	2.8443919
387889			63	0

Many thanks, BR Simon

Re: Spread whit DAX

123abc — Wed, 13 Dec 2023 07:25:09 GMT

you can achieve this by creating a new column that calculates the allocated cost for each product. Assuming your table is named YourTableName, you can use the following DAX formula:

AllocatedCost =
VAR TotalCostWithoutProduct = CALCULATE(SUM(YourTableName[Cost]), FILTER(YourTableName, YourTableName[Product] = BLANK()))

RETURN
IF(
NOT(ISBLANK(YourTableName[Product])),
YourTableName[Cost],
YourTableName[Cost] * (YourTableName[Import] / TotalCostWithoutProduct)
)

This formula creates a new column called AllocatedCost. It calculates the total cost without a specified product for each UniqueDutyNumber using the TotalCostWithoutProduct variable. Then, for each row in the table, it checks if the product is specified. If it is, it simply uses the original cost. If not, it allocates the cost based on the proportion of the product's import value compared to the total cost without a specified product.

Please replace YourTableName with the actual name of your table.

Note: This formula assumes that the import value is used as the allocation factor. You may need to adjust it based on your specific allocation criteria.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Spread whit DAX

Simon_Br — Wed, 13 Dec 2023 07:59:48 GMT

Hi,

Thank you for the answer. Can you make the formula whit my columne-name?

I have edit the uniquenumber, then this is the key for the spread.

BR Simon

Re: Spread whit DAX

Simon_Br — Thu, 14 Dec 2023 08:09:38 GMT

Unfortunately, this does not work as desired. The total value is created with all costs and by unique number. The ratio of export costs per UniqueNumber is also not created. Can anyone help me? I am very thankful!

Best Regards, Simon