Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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 )
)
)
)
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |