Forum Discussion

ExcelMonke's avatar
ExcelMonke
Icon for Impactful Individual rankImpactful Individual
2 years ago

SUMX Help - error in syntax?

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?

Need Help

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      ExcelMonke
      Icon for Impactful Individual rankImpactful Individual
      2 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.