Can anybody please help me to fix my attached rate and dups?

[Anonymous]

Hello, 

 

Can anybody please help me to fix my attached rate and duplicate issue on Department? 

 

I trying to get the attached rate by Department (Ops_Line_dim.Dept). 

 

When I join Opportunity Line (Ops_Line_dim) to the Opportunity table (Opportunity _dim) it will create duplicates due to Department (it's going to create two raws, each for the department). When I create my attached rate this will cause everything to go wrong. 

AttachRate = DISTINCTCOUNT('Opportunity _dim'[Ops_Line_dim.Opp.Id])/DISTINCTCOUNT('Opportunity _dim'[Id])

I am not sure to fix this, do I have to create a different column for each department or consider this on visualizing the data. 

It will probably make sense when you see my data, so please below for the attached data

Data

https://drive.google.com/file/d/1aP4oRJ9Ba90yFOacP7GLqoBehtSza-em/view?usp=sharing 

Pbix File

https://drive.google.com/file/d/1CuBKqNJrG_7ObaRNeQvG0lVHqW16fJIz/view?usp=sharing 

Any help would be appreciated. I am kinda stuck and could not move forward. 

[Ashish_Mathur]

Hi,

Could you please explain what attachrate is?  I can see from your formula that you are dividing 2 distinctcount of ID measures but that should be 100% - so i do not see a problem.  Perhaps, i need to understand what attach rate actually is.

[Anonymous]

@Ashish_Mathur , Thank you for taking the time to review my post. 

Attachrate is the formula that I used to create the attach rate. I don't think it is working right, as you can see it showing 100% for every department. The way it works, the Opportunity table (Opportunity _dim) has all the opportunities and the opportunity line has what we win. I did a left join to the opportunity by opportunity ID. This will give all the IDs that are matching with opportunity and also null IDs as well. Basically, I will calculate all the nulls and matching ids. The issue is the opportunity iAd can have multiple ids's when join. I think This is causing the issue. 

I don't know if this makes sense for you, but it will be easier to understand if you take look at my Power Bi file. If you have time. 

Any idea to fix this will be really appreciated. 

[Ashish_Mathur]

Hi,

In the Opportunity_dim table, there should be a Department column.

[Anonymous]

@Ashish_Mathur Opportunity table does not come with a  Department column. Is there a way we can create a separate column for each department? I am not sure just wondering how to do this. Thanks 

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

AttachRate = DISTINCTCOUNT('Opportunity _dim'[Ops_Line_dim.Opp.Id])/countrows('Opportunity _dim')

Hope this helps.

[Anonymous]

@Ashish_Mathur  Thank you so much, this looks promising. So you did join with two tables on Manage Relationships? 

[Ashish_Mathur]

No.  I just edited your measure (posted in the previous message).

[Anonymous]

@Ashish_Mathur Again, thank you for the quick response. I was working on it and, 

In this case, it is going to count duplicates right and give us a kinda wrong attach rate. Please correct me If I am wrong if Opttunity Line has two departments ( For Ex, Opps ID  xyz = product and xyz = Education) Then we join this to matin Opttunitry table, it will show 2 rows. When we do a" countrows",  it will take/count duplicates as well right? Please let me know if I am confusing you. 

[Ashish_Mathur]

Hi,

In the denominator, it will simply count all the rows in the Opportunity_Dims Table. 

[Anonymous]

@Ashish_Mathur  Thank you so much for your help. this works. I really appreciate your time. 

[Ashish_Mathur]

You are welcome.