Forum Discussion
Cumulative Sales Calculation
Hi Expert
I am trying to work out the cumulative sales divided by the Cumulative Complaints in Power BI, i can work out the formula in Excel fine (see image)
The problem arises in Power BI, I am pretty sure i have the correct DAX.
Main Formula.
Cumulative Complaint Rate = IF(DIVIDE([Cumulative Complaints],[Cumulative Sales],0)<>0,DIVIDE([Cumulative Complaints],[Cumulative Sales],0) )
Cumulative Sales and Complaint are calculated as follows:
Cumulative Complaints = CALCULATE([PMSComplaintsMN],
FILTER(ALL(PMS_FINANCIAL_PDS[FISCAL_MON_START_DT]),
PMS_FINANCIAL_PDS[FISCAL_MON_START_DT] <= MAX(PMS_FINANCIAL_PDS[FISCAL_MON_START_DT])
))
Cumulative Sales = CALCULATE([Sales],
FILTER(ALL(PMS_FINANCIAL_PDS[FISCAL_MON_START_DT]),
PMS_FINANCIAL_PDS[FISCAL_MON_START_DT] <= MAX(PMS_FINANCIAL_PDS[FISCAL_MON_START_DT])
))The main formula just gives the in month complaints/in month sale as a percentage.
see attached PBIX (file is missing sales data - you can make this up). rest of the test data is here.
https://www.dropbox.com/s/qedzqlc8eltztqz/Test.pbix?dl=0
Hi Anonymous
By my test, you didn’t calculate the cumulative sales and complaints. You still need other formulas. The following are the formulas you can have a try.
1.Cumulative Complaints =
CALCULATE (
SUM ( Table1[Complaints] ),
FILTER (
ALL ( Table1[Month Start] ),
Table1[Month Start] <= MAX ( Table1[Month Start])
)
)
2.Cumulative sales =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER (
ALL ( Table1[Month Start] ),
Table1[Month Start] <= MAX ( Table1[Month Start])
)
)
3.Cumulative Complaint Rate =
IF (
DIVIDE ( [Cumulative Complaints], [Cumulative sales], 0 ) <> 0,
DIVIDE ( [Cumulative Complaints], [Cumulative sales], 0 )
)
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
v-xuding-msftCommunity Support
Hi Anonymous
By my test, you didn’t calculate the cumulative sales and complaints. You still need other formulas. The following are the formulas you can have a try.
1.Cumulative Complaints =
CALCULATE (
SUM ( Table1[Complaints] ),
FILTER (
ALL ( Table1[Month Start] ),
Table1[Month Start] <= MAX ( Table1[Month Start])
)
)
2.Cumulative sales =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER (
ALL ( Table1[Month Start] ),
Table1[Month Start] <= MAX ( Table1[Month Start])
)
)
3.Cumulative Complaint Rate =
IF (
DIVIDE ( [Cumulative Complaints], [Cumulative sales], 0 ) <> 0,
DIVIDE ( [Cumulative Complaints], [Cumulative sales], 0 )
)
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.