Forum Discussion
Solved
Calculating Cumulative Total with the most updated values
Hi everyone, I'm new to Power BI and now I'm facing a problem about the Cumulative Total. The below table shows the information about number of products at certain timepoints sort by Product Type. T...
Hi, please try with this measure:
cumulative = VAR vtime = MAX ( Table1[TIME] ) RETURN CALCULATE ( SUMX ( SUMMARIZE ( Table1, Table1[REGION], Table1[TYPE], "MXamount", SELECTCOLUMNS ( TOPN ( 1, Table1, Table1[TIME] ), "AM", Table1[AMOUNT] ) ), [MXamount] ), FILTER ( ALLEXCEPT ( Table1, Table1[REGION] ), Table1[TIME] <= vtime ) )let me know if works
Victor
Lima-Peru
Thanks for your reply! I think your advise can help me to obtain the final number of products at the end of the day. But I also want to get the data throughout the day, so I can know how many products are in the store at each time point.
Actually I am not sure if power bi desktop can really deal with this situation. As I know power bi desktop is working on column level but my problem is on cell level (once encounter the repeated types of product, ignore the value use before and do calculation with the new value).
What do you think? I would appreciate any ideas!
Vvelarde
Community Champion
Community Champion
Hi, please try with this measure:
cumulative =
VAR vtime =
MAX ( Table1[TIME] )
RETURN
CALCULATE (
SUMX (
SUMMARIZE (
Table1,
Table1[REGION],
Table1[TYPE],
"MXamount", SELECTCOLUMNS ( TOPN ( 1, Table1, Table1[TIME] ), "AM", Table1[AMOUNT] )
),
[MXamount]
),
FILTER ( ALLEXCEPT ( Table1, Table1[REGION] ), Table1[TIME] <= vtime )
)
let me know if works
Victor
Lima-Peru
