Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
179 | |
108 | |
104 | |
71 | |
70 |