Forum Discussion

acanepa's avatar
acanepa
Resolver I
10 years ago
Solved

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_Deckler's avatar
    Greg_Deckler
    Community 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.