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 after is as shown in the image

 

 

 

 

 

 

 

 

 

 

 


My DAX for cumulative is:
Cumulative Sales = CALCULATE([TotalSalesTrend],
FILTER(
ALLSELECTED(PMS_FINANCIAL_PDS),
PMS_FINANCIAL_PDS[Month Start] <= MAX(PMS_FINANCIAL_PDS[Month Start])
))
My DAx for cumulative complaints is:
Cumulative Complaints = CALCULATE([TotalComplaintswithTrends],
FILTER(
ALLSELECTED(PMS_FINANCIAL_PDS),
PMS_FINANCIAL_PDS[Month Start] <= MAX(PMS_FINANCIAL_PDS[Month Start])
))
then i have created the following formula in order to plot this on a line graph


Cumulative Complaint Rate = IF(DIVIDE([Cumulative Complaints],[Cumulative Sales],0)<>0,DIVIDE([Cumulative Complaints],[Cumulative Sales],0)
)+0

my value are incorrect after the first data point.

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.

10 Replies

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

    Hi Anonymous ,

     

    Based on my test, your formula works fine and return the expected result on my side, we use the following formula for TotalSalesTrend and TotalComplaintswithTrends:

     

     

    TotalSalesTrend = SUM(PMS_FINANCIAL_PDS[Sales])

     

     

     

    TotalComplaintswithTrends = SUM(PMS_FINANCIAL_PDS[Complaints])

     

     

     


    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
      Hi Microsoft team

      I agree with your method, but I am getting an additional sum when I use the formula, I do have zero sum data points in my sales table? Which might be causing my cumulative sum error not so how to treat and or handle the zeros
      • 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.