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 )
        )
    )
)
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi MagikJukas 

As you can see the first argument of SUMMARIZE is 'Data' table. Therefore, only the rows available in the Data table will exist in the sammary table. 

you can try CROSSJOIN wrapped by ADDCOLUMNS. Or simply use SAMMARIZECOLUMNS 

Table =
SUMMARIZECOLUMNS (
'Date list'[Date],
Data[Material],
"tot",
CALCULATE (
SUM ( Data[Qty] ),
FILTER (
ALLSELECTED ( 'Date list'[Date] ),
'Date list'[Date] <= MAX ( 'Date list'[Date] )
),
ALL ( Data[Data type] )
) + 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 )
        )
    )
)