Ratio Within Same Column (Across Multiple Categories)

Question

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).&nbsp;&nbsp;&nbsp;I was able to create a measure that can track one of the conversion percentage (DAX below).&nbsp;&nbsp;=DIVIDE(CALCULATE(SUM([Daily COUNT]),[Group]="Appointment"),CALCULATE(SUM([Daily COUNT]),[Group]="Intake"))&nbsp;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!&nbsp;&nbsp;

v-yuta-msft · Accepted Answer

Hi awilliams91,
&nbsp;
Click query editor-&gt; Add Column-&gt; Index Column, after applied&amp;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] &lt;&gt; "intake", Table1[Daily Count] / previous_count, BLANK())
&nbsp;
&nbsp;
You can also refer to the attachment.
&nbsp;
Regards,
Jimmy Tao

Forum Discussion

Related content

Calculating cumulative total across categories

Count across multiple columns

Stacked Area Chart using cumulative total across multiple categories

How to create measures across multiple fact tables with multiple categories (fields/columns)?

Filtering across multiple dimensions