Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 imageCapture1.PNG

 

 

 

 

 

 

 

 

 

 

 


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.

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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.Capture.PNG

View solution in original post

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.

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.

View solution in original post

10 REPLIES 10
v-lid-msft
Community Support
Community Support

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

 

2.PNG3.PNG

 

 


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.

 

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
Not applicable

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

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

8.PNG

 


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.

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
Not applicable


You'll see the error here...

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.

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
Not applicable

Microsoft god bless thanks unsolicited much. Perfect. Can I ask u to delete the pbix file from the question please. Your reply.

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.

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
Not applicable

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?

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.

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
Not applicable

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.Capture.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.