Forum Discussion

MagikJukas's avatar
MagikJukas
Resolver III
3 years ago
Solved

Summarize table missing data

Hello, The following Table has been created: Table = SUMMARIZE(Data,'Date list'[Date],Data[Material],"tot",CALCULATE(SUM(Data[Qty]),FILTER(ALLSELECTED('Date list'[Date]),'Date list'[Date]<=MAX...
  • tamerj1's avatar
    tamerj1
    3 years ago

    MagikJukas 
    Like this?

    Measure = 
SUMX (
    ALLSELECTED ( Data[Material] ),
    CALCULATE (
        SUMX (
            VALUES ( 'Date list'[Date] ),
            VAR CurrentDate = 'Date list'[Date]
            VAR Tot =
                CALCULATE (
                    SUM ( Data[Qty] ),
                    'Date list'[Date] <= CurrentDate
                )
            RETURN
                IF ( Tot < 0, Tot, 0 )
        )
    )
)
MagikJukas's avatar
MagikJukas
Resolver III
3 years ago

Hello tamerj1 

fantastic, it works!

I am trying to use your code as a virtual table. I want to extract all the negative numbers in order to sum them up.

 

I got an error though, any idea or hints you can provide?

thank you 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

MagikJukas 
Please try

Measur1 =
SUMX (
    CROSSJOIN ( VALUES ( 'Date list'[Date] ), VALUES ( Data[Material] ) ),
    VAR Tot =
        CALCULATE (
            SUM ( Data[Qty] ),
            FILTER (
                ALLSELECTED ( 'Date list'[Date] ),
                'Date list'[Date] <= MAX ( 'Date list'[Date] )
            ),
            ALL ( Data[Data type] )
        ) + 0
    RETURN
        IF ( Tot < 0, Tot )
)
  • MagikJukas's avatar
    MagikJukas
    Resolver III
    3 years ago

    Hi again tamerj1 

    thanks for the code, but that does not work. the reuslts is showing a lot of blank rows.


    your code for the static table works exactly as I wanted. I just need to figure out how to replicate it has a virtual table.

     

    I am attaching the link of the file, I cleaned up the data to keep it light to the essential.

    https://drive.google.com/file/d/19tcvWiDSksTOsQcgdptCibiujXKO1vyh/view?usp=share_link

     

    I would appreciate if you gave it a look.

     

    regards

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      MagikJukas 
      Like this?

      Measure = 
SUMX (
    ALLSELECTED ( Data[Material] ),
    CALCULATE (
        SUMX (
            VALUES ( 'Date list'[Date] ),
            VAR CurrentDate = 'Date list'[Date]
            VAR Tot =
                CALCULATE (
                    SUM ( Data[Qty] ),
                    'Date list'[Date] <= CurrentDate
                )
            RETURN
                IF ( Tot < 0, Tot, 0 )
        )
    )
)
      • MagikJukas's avatar
        MagikJukas
        Resolver III
        3 years ago

        just fantastic!

        thanks a lot