Ratio Within Same Column (Across Multiple Categories)

[Anonymous]

I have a table that tracks a count of customers through our sales pipeline. Our sales pipeline is broken out into three different categories intake, appointment, and sale all grouped within a single column labeled Group. What I'm looking to accomplish is a way to create a conversion metric throughout the sales process. Specifically, I'm looking to create a metric that divides appointments by intake and another the divides sales by appointments (see below for screen of example data/table).

I was able to create a measure that can track one of the conversion percentage (DAX below).

=DIVIDE(CALCULATE(SUM([Daily COUNT]),[Group]="Appointment"),CALCULATE(SUM([Daily COUNT]),[Group]="Intake"))

However, I'm struggling to build out the logic within DAX to replicate this across all groups for their varying conversion (exclude intake). Is there a simple solution that I'm missing here that will allow me to, essentially, say if group=appointment then appointment count/intake count, if group=sale then sale count/appointment count, and if group=intake then blank() (like the table above)? Please let me know if you need any additional information. Thank you! 

[v-yuta-msft · Accepted solution]

Hi awilliams91,

Click query editor-> Add Column-> Index Column, after applied&closed, create a calculate column using DAX below:

Conversion = 
VAR previous_count = CALCULATE(SUM(Table1[Daily Count]), FILTER(Table1, Table1[Index] = EARLIER(Table1[Index]) - 1))
RETURN
IF(Table1[Group] <> "intake", Table1[Daily Count] / previous_count, BLANK())

 

You can also refer to the attachment.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft This worked out perfectly. Thank you for the help here!