create table with cumulative measures

[rufmau68]

I

im new in pbi

i need to define a new table in pbi model with this data:

i use summarize to define the first 3 columns but i dont know how to calculate the 4th column ("cumulated")

thanks falks!

[rufmau68]

i try it,

but dont run

when i use sumethinks like 

Y_cumulato =

CALCULATE(SUM([Y]),filter(ALLSELECTED(SparklineTable_Debug[Date]),SparklineTable_Debug[Date]<=MAX(SparklineTable_Debug[Date])))

i obtain the value not cumulated (Y=Y_cumulato)

 if i change formula like this, Y_cumulato = Y grand total 

For your information, my tables was generated using this script:

SparklineTable_Debug =

SUMMARIZE(CMSCardinis, CMSCardinis[Codice WBS], CMSCardinis[Mese di Rendicontazione].[Date], "Y", sum(CMSCardinis[Giornate]))

[v-rzhou-msft · Accepted solution]

Hi @rufmau68 

I think your CMSCardinis Table is the same as below.

Then you build a summarize table :SparklineTable_Debug.

SparklineTable_Debug =
SUMMARIZE(CMSCardinis, CMSCardinis[Codice WBS], CMSCardinis[Mese di Rendicontazione].[Date], "Y", sum(CMSCardinis[Giornate]))

To calculate the running total of Y for each Codice WBS, you could build a measure like amitchandak's reply, or you could build a calculated column.

Measure:

Basic Measure = 
CALCULATE (
    SUM ( [Y] ),
    FILTER (
        ALL ( SparklineTable_Debug ),
        SparklineTable_Debug[Codice WBS] = MAX ( SparklineTable_Debug[Codice WBS] )
            && SparklineTable_Debug[Date] <= MAX ( SparklineTable_Debug[Date] )
    )
)

Measure_Y_cumulato = SUMX(SparklineTable_Debug,[Basic Measure])

Basic will show wrong Total in table visual, so we need to build a new one basiced on Basic Measure.

Or you could build a calculated column in SparklineTable_Debug Table.

Column_Y_cumulato =
CALCULATE (
    SUM ( [Y] ),
    FILTER (
        SparklineTable_Debug,
        SparklineTable_Debug[Codice WBS] = EARLIER ( SparklineTable_Debug[Codice WBS] )
            && SparklineTable_Debug[Date] <= EARLIER ( SparklineTable_Debug[Date] )
    )
)

Result:

You can download the pbix file from this link: create table with cumulative measures

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@rufmau68 , Assume you measure is sum measure

Something like   sum(Table[Some value])

Try a new measure for Cumulative

calculate([measure], filter(allselected(Table]), table[category] = max(Table[category]) && table[data] <= max(Table[data])))