Help to translate a formula I have in Excel to do the same in power Bi

[Belle2024]

Hi,

I need help in recreating a formula I have in excel to power bi, I think the best way to explain it is to attach the files.

Unfortunately I can't attach it so I am hoping the link will work.

Basically I was to work out the scrap per machine based on the work order value.

Thanks in advance

[Belle2024]

Power bi example  

Thank you Sorry I just realised the links did not go on

Excel example 

[Belle2024]

Hi @burakkaragoz 

These are the links I previously supplied, I hope these help explaining it, I just included sheet 5 in the excel file so I could show the expected outcome.

[v-aatheeque]

Hi @Belle2024 ,
@burakkaragoz thanks for your prompt response. In addition to that ,
Thanks for sharing the file for better clarity. Based on your query, you’re looking to replicate the scrap calculation using the DAX formula previously shared by @burakkaragoz . Below is the expected output, as shown in the screenshot.

Hope this helps !

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[Belle2024]

Hi @v-aatheeque 

Thanks for your response,

This calculation is not getting me the values I require, the value I am looking for to be recreated in the power BI report should match the figure in the scrapped amount column in the excel file.

So basically, the calculation is the total value scrapped by process * (workorder value for that particular machine/ workorder value for the machines that are the same process in the machine category), and if there is no workorder value for it to divide the scrap amount between the machines in that process group

I hope this explains it better.
The scrapped amount column are the figures I am trying to replicate in power bi

[v-aatheeque · Accepted solution]

Hi @Belle2024 ,

Thanks for the clarification and image. Based on your explanation and the provided data,You want to allocate total scrapped amount per process group proportionally based on the Work Value of each machine in that group. If Work Value = 0, then the scrap amount should be evenly divided among the machines with zero work value in that process group.

Here is DAX Measure for allocated Scrap :

Allocated Scrap = 
VAR CurrentProcess = Sheet1[Process]
VAR MachineWork = Sheet1[Work Value]
VAR TotalProcessWork = 
    CALCULATE(
        SUM(Sheet1[Work Value]),
        ALLEXCEPT(Sheet1, Sheet1[Process])
    )
VAR MachineCount = 
    CALCULATE(
        COUNTROWS(Sheet1),
        ALLEXCEPT(Sheet1, Sheet1[Process])
    )
VAR TotalScrap = RELATED(Sheet2[TotalScrap])

RETURN
IF(
    TotalProcessWork > 0,
    TotalScrap * (MachineWork / TotalProcessWork),
    TotalScrap / MachineCount
)

the expected output :

FYI : I am sharing the sample PBIX file for better understanding as below.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[Belle2024]

Hi @v-aatheeque 

Thanks for your help with this, I am almost there with it the only think is the last part divides the total scrap by the number of processes whereas I need it to divide it by the number of machines that are in that same process group?

[v-aatheeque]

Hi @Belle2024 ,

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

[v-aatheeque]

Hi @Belle2024 ,

If you found the information helpful, please consider clicking "Upvote" and marking the response as "Accept Answer". This helps others in the community who may have similar questions find solutions more easily.

If you're still experiencing any issues or need further clarification, feel free to leave a comment we're happy to assist further.

Thank you for being an active part of the Microsoft Fabric Community and helping make it a valuable resource for everyone!

[v-aatheeque]

Hi @Belle2024 

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

[burakkaragoz]

Thanks a lot @v-aatheeque .

Appreciate you taking the time to validate and share the output – looks spot on.
Glad the DAX formula helped, and your table makes it even clearer for others who might run into the same scenario.

Let me know if you need help with anything else around this topic. @Belle2024 
translation and formatting supported by AI

[burakkaragoz]

Hi @Belle2024 ,

Sure, you can calculate scrap per machine in Power BI using DAX, similar to how you’d do it in Excel.

Assuming you have a table with columns like Machine, ScrapQty, and WorkOrderQty, you can create a measure like this:

ScrapPerMachine = 
DIVIDE(
    SUM('Table'[ScrapQty]),
    SUM('Table'[WorkOrderQty])
)

This will give you the scrap ratio per machine. You can then use this measure in a visual with Machine on the axis.

If your Excel formula is doing something more specific (like filtering by date or product), feel free to share the logic or a sample formula and I’ll help you translate it exactly.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[Belle2024]

Thank you @burakkaragoz 

I have attached the links now, hopefully that will help explain what I am wanting to do. I am wanting to replicate the formula in scrapped amount on the example tab.

Many thanks

[burakkaragoz]

Thanks for the update. I’ll check out the links you mentioned.

If you need help adapting the formula to your actual data structure or want to include filters (like per date or product), just let me know. Happy to help further.
translation and formatting supported by AI

[Belle2024]

Thanks for your help, the formula I am trying to replicate is 

=IFERROR(Sheet5!B2*($B2/SUM($B$2:$B$3)),Sheet5!B2/2)

So that this gives me the value in power bi that is in the column scrapped amount rather than it doing the calculation in excel

[burakkaragoz]

Thanks for sharing the formula @Belle2024,

Based on what you described, you're trying to replicate this logic in Power BI DAX:

=IFERROR(Sheet5!B2*(B2/SUM($B$2:$B$3)),Sheet5!B2/2)

Here’s how you can translate that into DAX, assuming you're working row by row:

Scrapped Amount = 
VAR CurrentValue = Sheet5[Value]
VAR Total = CALCULATE(SUM(Sheet5[Value]), ALL(Sheet5))
RETURN
IF(
    ISERROR(CurrentValue * (CurrentValue / Total)),
    CurrentValue / 2,
    CurrentValue * (CurrentValue / Total)
)

Replace Sheet5[Value] with your actual column name. If you're using this in a calculated column or measure, let me know and I can adjust it accordingly.

Let me know if you need help adapting it to your model.
translation and formatting supported by AI

[Belle2024]

Hi @burakkaragoz 

Thanks for looking at this for me, unfortunately this is still not calculating the way I need it too, I should have said that sheet 5 is not in the power bi report, I just have it for the excel version, there is a relationship between the material process and scrap tabs.

So what I'm trying to do is get the figures from column c in the excel file to be applied in the column beside the work order value in the power bi file. Hope this makes it clearer, thanks.

[v-aatheeque]

Hi @Belle2024 ,

In this scenario, Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

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

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community