Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate Cumulative value based on two variables

Hi Experts I am tryng to calculate the cumulative rate as shown in the explain below - which is pretty striaght forward in excel.  The first cell formula is 262/802338 then the formula there ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    hi team microsoft

    you formula is fine see my problem the graph to the left is correct the graph to the right is cumulative an is wrong.

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    When we calculate the culmulative sales, we can use the following measure, add the filter in Dim_Sales_Volume Table instead of PMS_FINANCIAL_PDS

     

    Cumulative Sales = CALCULATE([TotalSalesTrend],
    FILTER(
      ALLSELECTED(Dim_Sales_Volume),
    [Month Start] <= MAX(PMS_FINANCIAL_PDS[Month Start])
    )
    )

     

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

When we calculate the culmulative sales, we can use the following measure, add the filter in Dim_Sales_Volume Table instead of PMS_FINANCIAL_PDS

 

Cumulative Sales = CALCULATE([TotalSalesTrend],
    FILTER(
      ALLSELECTED(Dim_Sales_Volume),
    [Month Start] <= MAX(PMS_FINANCIAL_PDS[Month Start])
    )
)

 

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Microsoft god bless thanks unsolicited much. Perfect. Can I ask u to delete the pbix file from the question please. Your reply.
  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We have deleted the sample file and result picture from origin reply, OneDrive for business and local drive.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Team Microsoft

      did you change the regional date setting to make this work as i am getting a different answer and how did you do this?

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        We remember just change the measure formula without any filter applied or date field changed.

         

        Best regards,

        Community Support Team _ Dong Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.