Reply
Highlighted
Frequent Visitor
Posts: 10
Registered: ‎12-07-2018
Accepted Solution

Ratio Within Same Column (Across Multiple Categories)

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).

 

Capture.JPG

 

 

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! 

 


Accepted Solutions
Community Support Team
Posts: 2,481
Registered: ‎02-06-2018

Re: Ratio Within Same Column (Across Multiple Categories)

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())

Capture.PNG 

 

You can also refer to the attachment.

 

Regards,

Jimmy Tao

View solution in original post

Attachment

All Replies
Community Support Team
Posts: 2,481
Registered: ‎02-06-2018

Re: Ratio Within Same Column (Across Multiple Categories)

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())

Capture.PNG 

 

You can also refer to the attachment.

 

Regards,

Jimmy Tao

Attachment
Frequent Visitor
Posts: 10
Registered: ‎12-07-2018

Re: Ratio Within Same Column (Across Multiple Categories)

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