cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

You can also refer to the attachment.

Regards,

Jimmy Tao

2 REPLIES 2
Community Support Team

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

You can also refer to the attachment.

Regards,

Jimmy Tao

Frequent Visitor

## Re: Ratio Within Same Column (Across Multiple Categories)

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 141 members 1,851 guests
Recent signins: