Forum Discussion

Nicpet0's avatar
Nicpet0
Frequent Visitor
11 months ago
Solved

issues with RETURN statement from variables

Hi Community

im trying to create a DAX calculation that evaluates invoiced quantity based on my "Selection" variable. My issue is the return statement, where it wont divide my number with 1000 which i use to convert from kg to tonnes. How can i change my code to. Any tips for the overall structure of my code will also be helpful. 

Se snippet below for context. 

Thank you! 


 

DAX
variables
  • johnt75's avatar
    johnt75
    11 months ago

    I don't think that this is calculating what you want it to. The Total, Selection and External variables will not be recalculated for each iteration of Company Name. In DAX variables are not really variable, they are constants which are only calculated once.

    If you wish to recalculate for each company then you would need to move the variables inside the SUMX.

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    11 months ago

    Nicpet0 , Try using

     

    VAR Total =
    CALCULATE(
    [Invoiced Quantity (Base) LYTD],
    KEEPFILTERS(
    Customer[Customer Posting Group] IN {"Group", "Group_SBU", "Retail", "Wholesale", "Grower"}
    )
    )

    VAR External =
    CALCULATE(
    [Invoiced Quantity (Base) LYTD],
    KEEPFILTERS(
    Customer[Customer Posting Group] IN {"Retail", "Wholesale", "Grower"}
    )
    )

    VAR Selection =
    IF(
    SELECTEDVALUE('Type of Sales'[Intercompany Sales]) = "On",
    Total,
    External
    )

    RETURN
    SUMX(
    VALUES('Company'[Company Name]),
    IF(
    'Company'[Company Name] = "", // Ensure this condition is correct
    (Selection * 0.45359) / 1000,
    Selection / 1000
    )
    )

    • Nicpet0's avatar
      Nicpet0
      Frequent Visitor
      11 months ago

      Thanks your input

      Yes the condition is correct. Nothing changes for me, since the code is the exact same as before

  • johnt75's avatar
    johnt75
    Super User
    11 months ago

    I don't think that this is calculating what you want it to. The Total, Selection and External variables will not be recalculated for each iteration of Company Name. In DAX variables are not really variable, they are constants which are only calculated once.

    If you wish to recalculate for each company then you would need to move the variables inside the SUMX.

  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    10 months ago

    Hi Nicpet0 ,

    Thanks for reaching out to the Microsoft fabric community forum.

    johnt75 , bhanu_gautam , 

    Thanks for your prompt response

    Nicpet0 

     wanted to follow up and confirm whether you’ve had the opportunity to review the information  provided by  johnt75 , bhanu_gautam  . If you have any questions or need further clarification, please don’t hesitate to reach out.

     

    We appreciate your collaboration and support!

    Best regards,
    Lakshmi.

    • v-lgarikapat's avatar
      v-lgarikapat
      Community Support
      10 months ago

      Hi Nicpet0 ,

       

      We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

      We appreciate your engagement and thank you for being an active part of the community.


      Best Regards,
      Lakshmi.

      • v-lgarikapat's avatar
        v-lgarikapat
        Community Support
        10 months ago

        Hi @Nicpet0 ,

         

        We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

        We appreciate your engagement and thank you for being an active part of the community.


        Best Regards,
        Lakshmi.