Forum Discussion
Divide as Pivot Table
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
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.
1 Reply
- Greg_DecklerCommunity Champion
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.