Forum Discussion

hokitkan's avatar
hokitkan
Frequent Visitor
9 years ago
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...
General Comment
Need Help
Tutorial Requests
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    hokitkan

     

    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

hymieho's avatar
hymieho
Icon for Resolver I rankResolver I
9 years ago

Hi, I'm not exactly sure but I think you could RANK Type by Time and only SUM the top 1 (the latest value). 

  • hokitkan's avatar
    hokitkan
    Frequent Visitor
    9 years ago

    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's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      hokitkan

       

      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

      • hokitkan's avatar
        hokitkan
        Frequent Visitor
        9 years ago

        It works perfectly!

        Really impressed by the logic and thanks for your help!