Forum Discussion

uscmea's avatar
uscmea
Frequent Visitor
5 years ago
Solved

Cumulative Sum

Hi, I have three main measures in my data model:   sales = DISTINCTCOUNT(Sales[Product ID]) leads = DISTINCTCOUNT(Leads[Lead ID]) conv = IF([vacation_days]<7,[sales]/[leads],0)   The meas...
  • DataInsights's avatar
    DataInsights
    5 years ago

    uscmea,

     

    Try this measure:

     

    cum_conv = 
VAR vCurrentMonth =
    MAX ( 'Calendar'[Month Number] )
VAR vTable =
    ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Month Number] ), "@conv", [conv] )
VAR vResult =
    SUMX ( vTable, IF ( 'Calendar'[Month Number] <= vCurrentMonth, [@conv] ) )
RETURN
    vResult

     

     

DataInsights's avatar
DataInsights
Super User
5 years ago

uscmea,

 

I wasn't able to access the pbix via the link. This is the basic pattern for a cumulative sum:

 

cum_conv =
CALCULATE (
    [conv],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Month Number] <= MAX ( 'Calendar'[Month Number] )
    )
)

 

uscmea's avatar
uscmea
Frequent Visitor
5 years ago

Hi DataInsights,

 

Thank you very much for your answer!

 

I tried before this formula, but for some reason, it is not working.

 

I also tried SUMX = (FILTER (ALL('Calendar'),'Calendar'[Month Number] <=MAX('Calendar'[Month Number]), [conv])), but I still do not obtain the desired result.


I shared the Power BI model via Drive, let me know if you can download it from there. 

Best regards,

Sara

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    uscmea,

     

    Try this measure:

     

    cum_conv = 
VAR vCurrentMonth =
    MAX ( 'Calendar'[Month Number] )
VAR vTable =
    ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Month Number] ), "@conv", [conv] )
VAR vResult =
    SUMX ( vTable, IF ( 'Calendar'[Month Number] <= vCurrentMonth, [@conv] ) )
RETURN
    vResult

     

     

    • uscmea's avatar
      uscmea
      Frequent Visitor
      5 years ago

      Thank you very much for your help, DataInsights !!