Forum Discussion

rufmau68's avatar
rufmau68
Helper III
5 years ago
Solved

create table with cumulative measures

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!

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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 

    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. 

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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])))

  • rufmau68's avatar
    rufmau68
    Helper III
    5 years ago

    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]))



    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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 

      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.