In the DAX measure, the total sum always appears as 0.

[Jayden1029]

I have written a DAX function based on whether there were results in the previous year, as shown below.

It seems to be calculated correctly for each item, but the total always shows 0. How can I calculate the total as the sum?

_Sum_QTY_Without_PY =

VAR Current_Period_Sum =

    SWITCH(

        'Period Calculation'[SelectedCalc],

        1, 'CM_bridge'[_Sum_QTY],

        2, CALCULATE( 'CM_bridge'[_Sum_QTY], DATESYTD('Calendar'[Date]) ),

        3, CALCULATE( 'CM_bridge'[_Sum_QTY], ALL('Calendar'[Month]) )

    )

VAR PY_Period_Sum =

    SWITCH(

        'Period Calculation'[SelectedCalc],

        1, CALCULATE( 'CM_bridge'[_Sum_QTY], SAMEPERIODLASTYEAR('Calendar'[Date]) ),

        2, CALCULATE( CALCULATE('CM_bridge'[_Sum_QTY], SAMEPERIODLASTYEAR('Calendar'[Date])), DATESYTD('Calendar'[Date]) ),

        3, CALCULATE( CALCULATE('CM_bridge'[_Sum_QTY], SAMEPERIODLASTYEAR('Calendar'[Date])), ALL('Calendar'[Month]) )  

    )

RETURN

    IF(

        ISBLANK(PY_Period_Sum) || PY_Period_Sum = 0,

        Current_Period_Sum,

        0

    )

[v-kpoloju-msft]

Hi @Jayden1029,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @Anonymous, for your inputs on this issue.

Your measure logic looks correct for individual rows, but DAX evaluates totals differently it doesn’t sum individual rows. It recalculates in the total context, which is why you're getting 0.

To fix this, wrap your logic in a SUMX with VALUES([Country]), which forces it to evaluate per country and then aggregate:

_Sum_QTY_Without_PY =

SUMX(

    VALUES('YourTable'[Country]),  -- Replace with actual table name

    VAR Current_Period_Sum =

        SWITCH(

            'Period Calculation'[SelectedCalc],

            1, 'CM_bridge'[_Sum_QTY],

            2, CALCULATE('CM_bridge'[_Sum_QTY], DATESYTD('Calendar'[Date])),

            3, CALCULATE('CM_bridge'[_Sum_QTY], ALL('Calendar'[Month]))

        )

    VAR PY_Period_Sum =

        SWITCH(

            'Period Calculation'[SelectedCalc],

            1, CALCULATE('CM_bridge'[_Sum_QTY], SAMEPERIODLASTYEAR('Calendar'[Date])),

            2, CALCULATE(CALCULATE('CM_bridge'[_Sum_QTY], SAMEPERIODLASTYEAR('Calendar'[Date])), DATESYTD('Calendar'[Date])),

            3, CALCULATE(CALCULATE('CM_bridge'[_Sum_QTY], SAMEPERIODLASTYEAR('Calendar'[Date])), ALL('Calendar'[Month]))

        )

    RETURN

        IF(

            ISBLANK(PY_Period_Sum) || PY_Period_Sum = 0,

            Current_Period_Sum,

            0

        )

)

If you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Jayden1029,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @Jayden1029,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @Jayden1029,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Anonymous]

Hi @Jayden1029 

Please try this:
Create a new measure:

MEASURE =
VAR _vtable =
    SUMMARIZE (
        'Period Calculation',
        [Country],
        "__Sum_QTY", [_Sum_QTY_AC],
        "__Sum_Qty_py", [_Sum_Qty_py],
        "__Sum_QTY_Without_PY", [_Sum_QTY_Without_PY]
    )
RETURN
    SUMX ( _vtable, [__Sum_QTY_Without_PY] )

Hope this can help,

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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

[Jayden1029]

Thank you for your answer!

However, I have several "Key columns" such as 'Product, 'Dealer, 'Country," and so on.

I want to put the dynamic logic here.

For instance, if I add 'Dealer' and 'Country' to the table report, I want to see the result of the Dax measure considering 'Dealer' and 'Country'.

[raw data format]

[Jayden1029]

Plz help me to solve this problem.. if you need any further more information, please let me know!