Re: New column not giving correct results when usi...

Belle2015

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.

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

Stiffi88

Hi Belle2015,

I guess, you have to expant you ALLEXEPT with the Date. But here ist a very good article about this topic.

Using ALLEXCEPT versus ALL and VALUES - SQLBI

KR
Clemens

Elena_Kalina

Hi @Belle2015

You need to try modifying your DAX formula to respect the date context.

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], 'Date'[Date]) // Include date table in context
    )
VAR MachineCount =
    CALCULATE(
        DISTINCTCOUNT('Work Order Value'[Machine]),
        ALLEXCEPT('Work Order Value', 'Work Order Value'[Process], 'Date'[Date]) // Include date table in context
    )
VAR TotalScrap = 
    CALCULATE(
        SUM('Scrap'[Value Scrapped]),
        ALLEXCEPT('Scrap', 'Scrap'[Process], 'Date'[Date]) // Include date table in context
    )

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

If you don't have a date table, you could use the month columns directly (though a date table is recommended)

Allocated Scrap =
VAR CurrentProcess = 'Work Order Value'[Process]
VAR CurrentMonth = SELECTEDVALUE('Work Order Value'[Month]) // Or your month column
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'[Month]),
        'Work Order Value'[Month] = CurrentMonth
    )
// Similar modifications for other variables

Belle2015

Hi @Elena_Kalina,

Thanks for the help.

I have tried the above with my calendar table but it then doesnt give any value for the scrap in my table?

bnjmnnl

It may have something to do with the ALLEXCEPT function in your measure. Try adding your date within this function, as this function removes other filters.
Please let me know if this works?