Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

 

 

 

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! 

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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