cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Route217 Member
Member

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

Accepted Solutions
Route217 Member
Member

Re: Calculate Cumulative value based on two variables

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

v-lid-msft Super Contributor
Super Contributor

Re: Calculate Cumulative value based on two variables

Hi @Route217 ,

 

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.

View solution in original post

10 REPLIES 10
v-lid-msft Super Contributor
Super Contributor

Re: Calculate Cumulative value based on two variables

Hi @Route217 ,

 

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.

 

Route217 Member
Member

Re: Calculate Cumulative value based on two variables

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 Super Contributor
Super Contributor

Re: Calculate Cumulative value based on two variables

Hi @Route217 ,

 

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.

Route217 Member
Member

Re: Calculate Cumulative value based on two variables

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

Route217 Member
Member

Re: Calculate Cumulative value based on two variables


You'll see the error here...

v-lid-msft Super Contributor
Super Contributor

Re: Calculate Cumulative value based on two variables

Hi @Route217 ,

 

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.

View solution in original post

Route217 Member
Member

Re: Calculate Cumulative value based on two variables

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 Super Contributor
Super Contributor

Re: Calculate Cumulative value based on two variables

Hi @Route217 ,

 

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.

Route217 Member
Member

Re: Calculate Cumulative value based on two variables

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?

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 321 members 3,134 guests
Please welcome our newest community members: