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('Date list'[Date])),ALL(Data[Data type]))+0)

 However, when I look at the results, I can see only few entries:

 

Since I calculate a cummulative number for each day, I was expecting to find the table similar to then one that shows up in the visualization. Instead, the Summarize function only takes the "physical" records and it overlooks the previous days values.

 

Is there a way to tell the summarize function to populate every day with the cummulative value?

 

thanks

  • 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 )
        )
    )
)

6 Replies

  • 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 )
)