Forum Discussion

yve214's avatar
yve214
Icon for Helper III rankHelper III
4 years ago
Solved

Sumx function isn't working for my view

Hi there,   please help me.   my sumx function isnt working for a total qty measure i calculated.   I have my Qty measure where I am replacing my empty rows with the previous values as such...
matrix view
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi yve214 
    Please use

    Final Qty = 
SUMX (
    SUMMARIZE( Data, Data[Date], Data[Category] ),
    CALCULATE (
        VAR CurrentDate = MAX ( Data[Date] )
        VAR OriginalQty = SUM ( Data[Original Quantity] )
        VAR CurrentCategoryTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category] ) )
        VAR PreviousDatesTable = FILTER ( CurrentCategoryTable, Data[Date] < CurrentDate )
        VAR PreviousDate = MAXX ( PreviousDatesTable, Data[Date] )
        VAR PreviousQty = CALCULATE ( SUM ( Data[Original Quantity] ), Data[Date] = PreviousDate )
        RETURN
            IF (
                ISBLANK ( OriginalQty ),
                PreviousQty,
                OriginalQty
            )
    )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi yve214 
Please use

Final Qty = 
SUMX (
    SUMMARIZE( Data, Data[Date], Data[Category] ),
    CALCULATE (
        VAR CurrentDate = MAX ( Data[Date] )
        VAR OriginalQty = SUM ( Data[Original Quantity] )
        VAR CurrentCategoryTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category] ) )
        VAR PreviousDatesTable = FILTER ( CurrentCategoryTable, Data[Date] < CurrentDate )
        VAR PreviousDate = MAXX ( PreviousDatesTable, Data[Date] )
        VAR PreviousQty = CALCULATE ( SUM ( Data[Original Quantity] ), Data[Date] = PreviousDate )
        RETURN
            IF (
                ISBLANK ( OriginalQty ),
                PreviousQty,
                OriginalQty
            )
    )
)
  • yve214's avatar
    yve214
    Icon for Helper III rankHelper III
    4 years ago

    tamerj1 Thank you so much again for your help. This worked like magic.. Amazing!