Forum Discussion

yavuzonaran's avatar
yavuzonaran
Frequent Visitor
2 years ago

Production planning practice

Hello,

 

I want to calculate yellow areas in the table could anyone help me?

 

 

Tutorial Requests

7 Replies

  • foodd's avatar
    foodd
    Community Champion
    2 years ago

    Hello yavuzonaran , remember to adhere to the decorum of the Community Forum when asking a question.

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi yavuzonaran ,

     

    foodd , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is the sample data:

     

     

    You can try below formula to create a measure:

    Need to buy = SUMX (

    'Table',

    IF (

        'Table'[Amount] > 'Table'[Consumption],

        0,

        'Table'[Consumption] - 'Table'[Amount]

    )

)

     

    The final result is shown in the figure:

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    • yavuzonaran's avatar
      yavuzonaran
      Frequent Visitor
      2 years ago

      Hello Anonymous Thank you for your reply. There is a relationship between first table's first column and second table's first column and icant break it because it affects rest of document. So, is there any other solution for this?

      Thanks in advance 🙏🙏

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi yavuzonaran ,

        I apologize for my late reply. Thank you very much for your reply. Following your description, I recreated the two tables that are related:

         

         

         

        The relationship between them is:

         

         

        Here is my solution:

         

        1.Create two measures:

         

         

         

        Production Plan = MAX('Table'[Production Plan1])

Consumption = INT(MAX('Table'[Amount])*'Table'[Production Plan])

         

         

         

         

        2. You can try below formula to create the measure “need to buy”:

         

         

         

        Need to Buy = SUMX('Table (2)',IF('Table (2)'[Amount]<'Table'[Consumption],'Table'[Consumption]-'Table (2)'[Amount],0))

         

         

         

        3.The final result is as follows:

         

         

        Best Regards,
        Zhu
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!