SUMX Help - error in syntax?

[ExcelMonke]

Hello,
I am having some trouble with SUMX. My intended result seems to work in Excel - but not in Power BI. What i need to do, is effectively sum all the individual pieces in the column for each day. In this case, the total sum for 10/3/2023 should be 480, but it is showing 920. See the first screenshot below.

Screenshot 1:

The DAX For this is as follows:

Max Slot Length =
VAR _MaxSlot =
    CALCULATE (
        MAX ( 'Table'[SlotLength] ),
        FILTER (
            'Table',
            'Table'[Include/Exclude] = "Include" 
        ),
        ALLEXCEPT (
            'Table',
            'Table'[SlotDate],
            'Table'[SlotTime]
        )
    )
VAR _SumTable =
    SUMMARIZE (
        'Table',
        'Table'[SlotDate],
        'Table'[SlotTime],
        "Max Length", _MaxSlot
    )
RETURN
    CALCULATE (
        SUMX ( _SumTable, [Max Length] ),
        ALLEXCEPT ( 'Table', 'Table'[SlotDate] )
    )

The intended result is this:

Can anyone help me with this?

[DataNinja777]

Hi @ExcelMonke ,

What about sumxing your result over the SlotTime field like below? 

Max Slot Length =
VAR _MaxSlot =
    CALCULATE (
        MAX ( 'Table'[SlotLength] ),
        FILTER ( 'Table', 'Table'[Include/Exclude] = "Include" ),
        ALLEXCEPT ( 'Table', 'Table'[SlotDate], 'Table'[SlotTime] )
    )
VAR _SumTable =
    SUMMARIZE (
        'Table',
        'Table'[SlotDate],
        'Table'[SlotTime],
        "Max Length", _MaxSlot
    )
RETURN
    SUMX (
        VALUES ( 'Table'[SlotTime] ),
        CALCULATE (
            SUMX ( _SumTable, [Max Length] ),
            ALLEXCEPT ( 'Table', 'Table'[SlotDate] )
        )
    )

 Best regards,

[ExcelMonke]

Thank you for the suggestion! Unfortunately this overestimates it even more (See Max Slot Length2)

[Anonymous]

Hi,@ExcelMonke. I am glad to help you.
Hello,@DataNinja777 ,thanks for your concern about this issue.

Has your problem been solved yet, if not, can you provide a .pbix file that does not contain sensitive data, this will help to solve your problem.

Could you please check if your measure calculations are due to an incorrect current calculation environment, and secondly check the code itself to see if there are any problems. Because the filter settings including the relationship filter between tables in the model will also affect the measure calculation result.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ExcelMonke]

Hello,

Unfortunately I am unable to share the relatively complex PBIX. I am hoping there's a relatively simple solution to this SUMX issue I am facing.