Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
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.
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.
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.
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.
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.
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |