cancel
Showing results for
Did you mean:
Highlighted
Member

## 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Member

## Re: Cumulative Sales Calculation

PS. I have already sum'd sales and complaints using a seperate Measure.

Established Member

## Re: Cumulative Sales Calculation

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 3
Member

## Re: Cumulative Sales Calculation

PS. I have already sum'd sales and complaints using a seperate Measure.

Established Member

## Re: Cumulative Sales Calculation

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.

Member

## Re: Cumulative Sales Calculation

Xue.. many thanks for the feedback. that works just fine.