Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
MagikJukas
Resolver III
Resolver III

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:

MagikJukas_0-1674228230050.png

 

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.

MagikJukas_1-1674228275733.png

 

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

 

thanks

1 ACCEPTED SOLUTION

@MagikJukas 
Like this?

1.png

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

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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
)

 

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 

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

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

 

@MagikJukas 
Like this?

1.png

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

just fantastic!

thanks a lot

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.