Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
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
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
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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |