Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
PS. I have already sum'd sales and complaints using a seperate Measure.
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.
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.
Xue.. many thanks for the feedback. that works just fine.
PS. I have already sum'd sales and complaints using a seperate Measure.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
102 | |
78 | |
77 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |