New column not giving correct results when using date to filter by month

[Belle2015]

Example power BI 

Excel Data 

I have had help to create the below DAX function - 

However I have run into a problem when I went to use it in my actual data where I want to apply a date filter to see the values by each 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 for the year to date but when you split it out by the months the values by the date are not correct. 

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(

    DISTINCTCOUNT('Work Order Value'[Machine]),

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

)

VAR TotalScrap = CALCULATE(

        SUM('Scrap'[Value Scrapped]),

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

    )

RETURN

IF(

    TotalProcessWork > 0,

    TotalScrap * (MachineWork / TotalProcessWork),

    TotalScrap / MachineCount

)

Many thanks in advance for your help. 

[Elena_Kalina]

Hello. Thank you for granting access to your PBIX file. The first thing I checked and noticed is that all relationships between your tables are bidirectional, which is already a red flag.

First, we need to restructure your schema. To do this, we need to create three dimension tables: DimMachine, DimProcess, and DimMaterial, and set up the relationships as shown in my screenshot.

Now, please, instead of a technical solution, share the logical question: what are you trying to find or achieve with this data model?

[Belle2015]

HI @Elena_Kalina , 

Thank you for your help. 

I am trying to get the value of scrap per machine.  Each material is categorised into a process and each machine is also categorised by process but there may be more than one machine for the process so I need the scrap by machine to be based on the work order value of each machine as well.

So for example there are three machines in the packing process but say only two of those machines were running so one machine has a workorder value of zero I want the scrap then to be zero for that machine as it wouldnt have contributed to scrap and then the scrap would be spilt between the other two machines based on the workorder value, so if one had a much larger workorder value a higher portion of the scrap will be allocated to it. 

I hope this makes sense. 

[Elena_Kalina]

Hi @Belle2015 

Based on the logic you described, there should be no scrap in January and February for the first process since the machines were not operating. And the total amount you calculated should be 81,233, not 90,634, as you expected.

Or did I misunderstand your approach?

[Belle2015]

Hi Elena @Elena_Kalina , 

There may still be scrap even if the machine isnt running as this is not the waste from the machine this is addition transactions for scrap, so in the case where no machines have been running in a process I just want to divide the scrap equally between the machines.

[Elena_Kalina]

Hi @Belle2015 

Sorry for the delayed response, I didn't have enough time. Try to reproduce my schema.

Here is the formula for the calculated column to establish the relationship between tables:

CompositeKey = 'Work Order Value'[Machine] & "|" & 'Work Order Value'[Process]

Next, create two measures:

Measure_Scrap =
VAR CurrentProcess = SELECTEDVALUE('Work Order Value'[Process])  
VAR CurrentDate = SELECTEDVALUE('Work Order Value'[Date])  
VAR CurrentMachine = SELECTEDVALUE('Work Order Value'[Machine])  
VAR MachineWO = CALCULATE(  
    SUM('Work Order Value'[Work Value]),  
    'Work Order Value'[Date] = CurrentDate,  
    'Work Order Value'[Machine] = CurrentMachine  
)  
VAR ProcessTotalWO = CALCULATE(  
    SUM('Work Order Value'[Work Value]),  
    FILTER(  
        ALL('Work Order Value'),  
        'Work Order Value'[Process] = CurrentProcess &&  
        'Work Order Value'[Date] = CurrentDate &&  
        'Work Order Value'[Work Value] > 0  
    )  
)  
VAR ScrapValue = SUM('Scrap'[Value Scrapped])  
VAR MachinesInProcessCount =   
    CALCULATE(  
        DISTINCTCOUNT('Work Order Value'[Machine]),  
        FILTER(  
            ALL('Work Order Value'),  
            'Work Order Value'[Process] = CurrentProcess &&  
            'Work Order Value'[Date] = CurrentDate  
        )  
    )  
RETURN  
    IF(  
        ISBLANK(CurrentMachine) || ISBLANK(CurrentProcess) || ISBLANK(CurrentDate),  
        0,  
        IF(  
            ProcessTotalWO = 0,  
            DIVIDE(ScrapValue, MachinesInProcessCount, 0),  
            DIVIDE(MachineWO, ProcessTotalWO, 0) * ScrapValue  
        )  
    )  

Measure_ScrapPerMachine_Final =
VAR IsMachineLevel = ISINSCOPE('Work Order Value'[Machine])  
VAR IsProcessLevel = ISINSCOPE('Work Order Value'[Process])  
VAR IsMonthLevel = ISINSCOPE('Calendar'[Month])  

// Calculation for the individual machine level  
VAR MachineLevelCalc =   
    VAR MachineWO = CALCULATE(SUM('Work Order Value'[Work Value]))  
    VAR ProcessTotalWO =   
        CALCULATE(  
            SUM('Work Order Value'[Work Value]),  
            ALL('Work Order Value'[Machine]),  
            'Work Order Value'[Work Value] > 0  
        )  
    VAR ScrapValue =   
        LOOKUPVALUE(  
            'Scrap'[Value Scrapped],  
            'Scrap'[Process], SELECTEDVALUE('Work Order Value'[Process]),  
            'Scrap'[Date], SELECTEDVALUE('Work Order Value'[Date])  
        )  
    VAR MachinesCount =   
        CALCULATE(  
            DISTINCTCOUNT('Work Order Value'[Machine]),  
            ALL('Work Order Value'[Machine])  
        )  
    RETURN  
        IF(  
            ISBLANK(MachineWO) || ISBLANK(ProcessTotalWO),  
            0,  
            IF(  
                ProcessTotalWO = 0,  
                DIVIDE(ScrapValue, MachinesCount, 0),  
                DIVIDE(MachineWO, ProcessTotalWO, 0) * ScrapValue  
            )  
        )  

// Calculation for the process and month level  
VAR ProcessMonthCalc =   
    SUMX(  
        SUMMARIZE(  
            'Work Order Value',  
            'Work Order Value'[Machine],  
            'Work Order Value'[Process],  
            'Work Order Value'[Date]  
        ),  
        [Measure_Scrap]  
    )  

// Calculation for the grand total  
VAR GrandTotalCalc =   
    SUMX(  
        CROSSJOIN(  
            VALUES('Work Order Value'[Process]),  
            VALUES('Work Order Value'[Machine]),  
            VALUES('Calendar'[Month])  
        ),  
        [Measure_Scrap]  
    )  

// Determine the level and return the result  
RETURN  
    SWITCH(TRUE(),  
        IsMachineLevel, MachineLevelCalc,  
        IsProcessLevel || IsMonthLevel, ProcessMonthCalc,  
        GrandTotalCalc  
    )  

Next, create a visual with columns for Process and Machine, taken from the Machine Category table.

If this answer was helpful to you, please consider giving it a 👍 (kudos) or marking it as the solution!

[v-mdharahman]

Hi @Belle2015,

The core of the issue lies in how the ALLEXCEPT function is being used. In your current formula, you’re preserving the [Process] filter, but this ends up removing the date filter entirely. So when you look at the values month by month, the measure is still calculating based on the full year’s total for TotalProcessWork, TotalScrap, and MachineCountwhich leads to distorted or incorrect results in monthly views.

To fix this, you’ll want to preserve the date context in those calculations so that the measure responds correctly when slicing by month (or any other time period).

Try this measure:

Allocated Scrap =
VAR CurrentProcess = 'Work Order Value'[Process]
VAR MachineWork = 'Work Order Value'[Work Value]
VAR TotalProcessWork =
CALCULATE(
SUM('Work Order Value'[Work Value]),
REMOVEFILTERS('Work Order Value'[Machine]) -- keep the date filter
)
VAR MachineCount =
CALCULATE(
DISTINCTCOUNT('Work Order Value'[Machine]),
REMOVEFILTERS('Work Order Value'[Machine]) -- keep the date filter
)
VAR TotalScrap =
CALCULATE(
SUM('Scrap'[Value Scrapped]),
REMOVEFILTERS('Scrap'[Machine]) -- remove machine filter, keep date
)

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

Best Regards,

Hammad.

[Belle2015]

Hi @v-mdharahman 

Thanks so much for your help with this Hammad. 

I have tried the above but it is now only showing the one process and one month and doesnt  have the correct figures for those. 

I did make one change to the formula as I did not have a column for machine in the scrap data

Appreciate your help on this 🙂

[v-mdharahman]

Hi @Belle2015,

Thanks for the following up with the conversation and for providing screenshots. The reason you’re only seeing one process and incorrect monthly values is because of this line "REMOVEFILTERS('Scrap'[Process])". 

This removes the process grouping, so all scrap data gets aggregated incorrectly. Since your Scrap table doesn’t have a Machine column, you don’t need to use REMOVEFILTERS here at all.

Instead, you should make sure both Process and Date filters are preserved in your calculations. Try the following updated formula:

Allocated Scrap =
VAR CurrentProcess = SELECTEDVALUE('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], 'Work Order Value'[Date])
)
VAR MachineCount =
CALCULATE(
DISTINCTCOUNT('Work Order Value'[Machine]),
ALLEXCEPT('Work Order Value', 'Work Order Value'[Process], 'Work Order Value'[Date])
)
VAR TotalScrap =
CALCULATE(
SUM('Scrap'[Value Scrapped]),
ALLEXCEPT('Scrap', 'Scrap'[Process], 'Scrap'[Date])
)
RETURN
IF(
TotalProcessWork > 0,
TotalScrap * (MachineWork / TotalProcessWork),
DIVIDE(TotalScrap, MachineCount)
)

This ensures that when you view the measure by month, the allocation happens only within that month and process, giving you the correct monthly split like in your expected table.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[v-mdharahman]

Hi @Belle2015,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[v-mdharahman]

Hi @Belle2015,

Thanks for reaching out to the Microsoft fabric community forum.

Just following up to your conversation, I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

I would also take a moment to thank @bnjmnnl, @Stiffi88 and @Elena_Kalina, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[Belle2015]

Hi @v-mdharahman , 

I have not resolved this issue yet, when I have applied the suggested I dont get any data showing. 

Any further help would be greatly appreciated.

[v-mdharahman]

Hi @Belle2015,

Can you please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Also show the expected outcome based on the sample data you provided.

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

Best Regards,

Hammad.

[v-mdharahman]

Hi @Belle2015,
Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if got a chance to look after the issue. If yes, kindly share a sample data that covers your issue or question completely, in a usable format so that we can work on your issue and provide an accurate solution.

Best Regards,

Hammad.

[Belle2015]

Hi @v-mdharahman 

I have still not resolved the issue I have hopefully been able to share the link successfully now 🙂

[Elena_Kalina]

Hi @Belle2015 

I’d love to help, but I’m running into an issue: the report file you sent won’t open for me. I tried using your second Excel file as a data source instead, but it’s missing some key columns you referenced in your measure('Scrap'[Process], 'Work Order Value'[Process])

Perhaps you have changed the tables in Power Bi. To definitely help you, I need to completely reproduce your problem for this, you need the same table structure as yours and the connection between them. Otherwise, I’m just guessing in the dark—and I’d hate to give you wrong advice!

[Belle2015]

Hi @Elena_Kalina , 

For the ('Scrap'[Process], 'Work Order Value'[Process]) that was mentioned I merged queries with the material process and the scrap to give me a column in the scrap data to provide the process for the material number. I hope this makes sense. 

Thanks

[Belle2015]

Hi @Elena_Kalina , 

Thanks for your help. 

Hopefully this link might work and help

Sample Power BI 

[Stiffi88]

Is it possible, that you can share a pbix file? So it would be easier to get a look on it.

[Belle2015]

@Stiffi88 Thanks for looking at this for me 

Not sure if this will work 

[Stiffi88]

Hi @Belle2015 for me this Link isn't working.