Show if a record exists in a another table

[gushal]

Hi,

I am new at Power BI and need some help. Following are my issue:

We have an opportunity table and one Sales order table. One opportunity can have several sales orders. 

I want to display in a matrix, how many of the opportunites that has a one or several sales orders connected to it, i.e. I don´t care if there are several sales orders, I just want to display if the opportunity has any sales orders or none at all. 

I want to display it in percentage of total count of opportunities. 

For example: In one store there are 5000 opportunities and 8000 sales orders. But only 3000 of these opportunities have connected sales orders, which means 60% of the opportunities have one or several sales orders:

How do I create this?

Thanks in advance

[v-easonf-msft]

Hi, @gushal 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft · Accepted solution]

Hi, @gushal 

Based on the information you currently provide, I made a  sample here.

Measure = 
var Measure1 = CALCULATE(DISTINCTCOUNT('Sales order table'[opportunities]),ALLEXCEPT('Sales order table','Sales order table'[Store]))
var Measure2 = CALCULATE(DISTINCTCOUNT('opportunity table'[opportunities]),ALLEXCEPT('opportunity table','opportunity table'[Store]))
return  Measure2/Measure1

If it doesn't meet you current requirement，please share information about your two tables.

Best Regards,
Community Support Team _ Eason

 

[AllisonKennedy]

You must have matching values in both tables - is there an Opportunity ID in the Sales Order table? How do you know if they are in both tables? 

Please share your sample data or at least the column and table names with some sample values.