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

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

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

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!