Forum Discussion

Roseventura's avatar
Roseventura
Icon for Responsive Resident rankResponsive Resident
1 year ago
Solved

Incorrect Totals when adjusting the value based on Year-Mo

Hi all,

 

I'm having a very common problem but can't get the correct results.  Basically, I need to adjust my credits if the month is the current month.  If it's not the current month, then don't adjust.  Instead, take the actual credits.

 

So looking below, if the month is the current month (June 2025), then adjust my credits (-493,597) by the Sales Adjustment (550,000).  Make the difference negative.  The column "Credits Less Credit Adjustment" shows the correct value for each row, but the total is incorrect.  It should be -950,387.36.  I'm guessing because I'm not forcing the measure to be additive?

 

 

Here is my measure:  

 

Credits Less Credit Adjusment =
 VAR SelYrMo = SELECTEDVALUE(PBI_FSCAPF[YEAR-MO])
 VAR SlsAdjust = [Selected Sales Adjustment]
 RETURN
 sumx(
    VALUES(PBI_FSCAPF[YEAR-MO]),
    calculate(
        if( SelYrMo <> [Current Year-Mo],
            [QTD Actual Credit Sales],
            -(SlsAdjust + [QTD Actual Credit Sales]
            )
        )
    )
)
 
Selected Sales Adjustment = SELECTEDVALUE('Credit Adjustments'[Sales Adjustment])
 
The credit adjustment is a simple table that stores adjustment values by month and date (joined with my date table).
 
Any help would be greatly appreciate.
Thank you.
Need Help
  • hnam_2006's avatar
    hnam_2006
    1 year ago

    Hi Roseventura 

     

    In DAX, when you use SUMX(VALUES...), the calculation is done row by row... even for totals. thats why you get wrong values.

    Try the following DAX... it should fix your problem

    Credits Less Credit Adjustment =
SUMX (
    ADDCOLUMNS (
        VALUES ( PBI_FSCAPF[YEAR-MO] ),
        "AdjustedCredit",
            IF (
                [YEAR-MO] = [Current Year-Mo],
                -([Selected Sales Adjustment] + [QTD Actual Credit Sales]),
                [QTD Actual Credit Sales]
            )
    ),
    [AdjustedCredit]
)

     

    Best Regards,

    hnam_2006

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

4 Replies

  • hnam_2006's avatar
    hnam_2006
    Frequent Visitor
    1 year ago

    Hi Roseventura 

     

    In DAX, when you use SUMX(VALUES...), the calculation is done row by row... even for totals. thats why you get wrong values.

    Try the following DAX... it should fix your problem

    Credits Less Credit Adjustment =
SUMX (
    ADDCOLUMNS (
        VALUES ( PBI_FSCAPF[YEAR-MO] ),
        "AdjustedCredit",
            IF (
                [YEAR-MO] = [Current Year-Mo],
                -([Selected Sales Adjustment] + [QTD Actual Credit Sales]),
                [QTD Actual Credit Sales]
            )
    ),
    [AdjustedCredit]
)

     

    Best Regards,

    hnam_2006

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

  • SamsonTruong's avatar
    SamsonTruong
    Icon for Super User rankSuper User
    1 year ago

    Hi Roseventura,

     

    Can you please try the adjusted measure for "Credits Less Credit Adjustment" and let me know if this achieves your desired result:

    Credits Less Credit Adjustment = 
VAR CurrentMonth = [Current Year-Mo]
RETURN
SUMX(
    VALUES(PBI_FSCAPF[YEAR-MO]),
    VAR ThisYrMo = PBI_FSCAPF[YEAR-MO]
    VAR Credit = CALCULATE([QTD Actual Credit Sales])
    VAR Adj = CALCULATE([Selected Sales Adjustment])
    RETURN
        IF(
            ThisYrMo = CurrentMonth,
            Credit - Adj,
            Credit
        )
)

     

    If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

    Thanks,

    Samson

     

    Connect with me on LinkedIn

    Check out my Blog

    Going to the European Microsoft Fabric Community Conference? Check out my Session

  • Ashish_Excel's avatar
    Ashish_Excel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hi,

    Drag this measure

    =SUMX(VALUES(Calendar[Year-Mo]),[Credits less credit adjustment])

    Hope this helps.