Count of machines in each Process

[Belle2015]

Hi,

I have had help to create the below DAX measure but the last part in orange is not working the way I need it too. 

I have a list of Machines that each fall into a certain process, there may be more than one machine in each process, what I need is a calculation I can use in the below where I need to divide the total scrap by the number of machines that come under the same process as at the minute the calculation is only dividing it by the total number of processes

Excel Data 

Power Bi example 

Allocated Scrap =

VAR CurrentProcess = 'Work Order Value'[Process]

VAR MachineWork = 'Work Order Value'[Work Value]

VAR TotalProcessWork =

    CALCULATE(

        SUM('Work Order Value'[Work Value]),

        ALLEXCEPT('Work Order Value', 'Work Order Value'[Process])

    )

VAR MachineCount =

    CALCULATE(

        COUNTROWS('Work Order Value'),

        ALLEXCEPT('Work Order Value', 'Work Order Value'[Work Value])

    )

VAR TotalScrap = CALCULATE(

        SUM('Scrap'[Value Scrapped]),

        ALLEXCEPT('Scrap', 'Scrap'[Process])

    )

RETURN

IF(

    TotalProcessWork > 0,

    TotalScrap * (MachineWork / TotalProcessWork),

    TotalScrap / MachineCount

)

[Belle2015]

Hi @burakkaragoz ,

Thanks so much this worked perfectly 🙂

[burakkaragoz]

@Belle2015 ,Happy to help! If you run into anything else, feel free to ask.
Thanks for the feedback! 😊

[Belle2015]

HI @burakkaragoz , 

I ran into a problem when I went to use this in my actual data where I want to apply a date filter to see the values by month, I hadnt included the dates in my sample data, would I need to write something into the calculation to have it calculating by the date as at the minute it gets the total of scrap by process correct but when you split it out by the date the values by the date are not correct. 

Example Power Bi 

Any help would be appreciated.

Bronagh

[burakkaragoz · Accepted solution]

Hi @Belle2015 ,

If you want to divide total scrap by the number of machines in each process (not by the number of total processes), your MachineCount measure needs to count the distinct machines for the current process.

Try changing your MachineCount to this:

dax

MachineCount =
CALCULATE(
    DISTINCTCOUNT('Work Order Value'[Machine]),
    ALLEXCEPT('Work Order Value', 'Work Order Value'[Process])
)

Then use:

dax

TotalScrap / MachineCount

This way, MachineCount will return the number of unique machines for each process, so your calculation will divide total scrap by the correct number.

Let me know if this helps or if you need to tweak it for your model!