Forum Discussion

Simon_Br's avatar
Simon_Br
Icon for Helper I rankHelper I
2 years ago

Spread whit DAX

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?

 

UniqueDutyNumberOrderNoOrder-Nr.ImportExport Solution 
343299Cola431110102 47%29.4075271
343299Fanta434880114.55 53%33.0258062
343299  62.43333333    
387889Beer4362659.7192.8666667 57%57.1446234
387889Wine8744561.683333333135.8833333 40%40.2609847
387889Water99329100.259.6 3%2.8443919
387889  630   

 

 

Many thanks, BR Simon

DAX

3 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.

    • Simon_Br's avatar
      Simon_Br
      Icon for Helper I rankHelper I
      2 years ago

      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

  • Simon_Br's avatar
    Simon_Br
    Icon for Helper I rankHelper I
    2 years ago

    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