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.
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. The number of products may fluctuate due to products sold or get inventory replenishment.
What I want to do is to calculate the Cumulative Total chronologically so I can obtain a output table as follow.
I found informations about how to calculate cumulative total by group using the CALCULATE(SUM(), FILTER(ALL(),
'Table'[Date] <= MAX('Table'[Date]))) Syntax. However, I cant figure out how to calculate the cumulative total when same type of products occur repeatedly with an updated numbers. How to ignore the old value and use the latest value encounter at the corresponding timepoints?
I cant find any useful information on the web. So any hints or advises would be much appericated!
Solved! Go to Solution.
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
Hi, I'm not exactly sure but I think you could RANK Type by Time and only SUM the top 1 (the latest value).
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!
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
Yes, thanks. And now I know about SELECTCOLUMNS, and I had not seen this pattern for adding individual columns back after summarizing--I have only seen calculated columns. Great stuff!
It works perfectly!
Really impressed by the logic and thanks for your help!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |