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 ,

 

Could you please try to use the following measure in the table visual and see if you can get the expected result? 

Can your measure Cumulative Sales and Cumulative Complaints get the expected result?

 

Cumulative Complaint Rate 2 = 
VAR t =
    FILTER (
        ALLSELECTED ( PMS_FINANCIAL_PDS ),
        PMS_FINANCIAL_PDS[Month Start] <= MAX ( PMS_FINANCIAL_PDS[Month Start] )
    )
VAR Complaints =
    CALCULATE ( SUM ( PMS_FINANCIAL_PDS[Complaints] ), t )
VAR Sales =
    CALCULATE ( SUM ( PMS_FINANCIAL_PDS[Sales] ), t )
RETURN
    DIVIDE ( Complaints, Sales, 0 ) + 0

 


BTW, pbix as attached.

 

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


You'll see the error here...

  • 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.