Divide as Pivot Table

[acanepa]

Hello,

I'm having problems with the division in PowerBI. The report is for repurchase rate. I have a database with a list of clients, where I have month of purchase, money spent and more. The most important is a binary column where 1 is client made a buy last 90 days. (RR_2)

I create a calculated field (column) where I want to divide two values (the division I wa

nt to obtain by month of the year).

1) Sum by month of binary column. This Field is called RR_2. The calculation of value 

2) The total sum of rows in a column. This Field is called RR_by_Month

2.1) Counter with value 1 in all rows.

When I make the division between this two fields I can't have the exact number. For example If I have 3000 clients with a repurchase and a total of 20.000 clients that month that have buy, the repurchase rate going to be 15% but intead the division give me 1,5%.

I ave tryed the next calcultations

divide(RR_2,countrows8'table')

divide(RR_2, counter)

divide(sum(RR_2),SUM(COUNTER))

DIVIDE(RR_2, COUNTA(DATE_FIELD))

DIVIDE(RR_2,AVERAGE(RR_BY_MONTH)

If I how the values by numerator and denominator is gives the numbers that I want but I divide the 2 numbers give me strange numbers, I think is for the level of aggregation of the DAX formulas that I don't undestand.

Here is a litle example of a table

Monthnumber  | RR2 | Counter| RR_BY_MONTH

01-11-2015      | 1      |    1        | 3

01-11-2015      | 0      |    1        | 3

01-11-2015      | 0      |    1        | 3

[Greg_Deckler · Accepted solution]

You should not need any column other than RR_2 and your date column. Create a measure with the following formula:

Measure = DIVIDE(SUM([RR_2]),COUNT([RR_2]))

Note that:

Measure = SUM(RR_2)/COUNT(RR_2])

will also work if you are not worried about divide by zero issues, which you probably don't need to worry about since you shouldn't have zero rows.