Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello!
I am new to Power BI and would really appreciate you help on the following question:
I have two tables: Registration Status (Approved, Declined, In Process), and Pipeline (Projected Sales Amount). I am looking for sum of sales amount for each registration status. Thank you!
Registration Status | |
Opp ID | Registration Status |
123456 | Approved |
234567 | Approved |
345678 | Approved |
456789 | Approved |
654321 | Declined |
765432 | Declined |
876543 | In Process |
987654 | In Process |
Pipeline | |
Opp ID | Amount |
123456 | 500 |
234567 | 600 |
345678 | 700 |
456789 | 800 |
654321 | 900 |
765432 | 100 |
876543 | 300 |
987654 | 200 |
WHen I tried using the same data, PowerBI automatically detected the relationships between these two tables. The resulting table provides what you want (right hand side)
Thank you Ceebu. I realized what the problem was. The amount field wasn't a direct value in the pipeline table, but a key measure based on a calculation of two fields in the pipeline table. It seems I can't manage the relationships between Registration Table and Key Measures, and when I try to drag amount to different registration status, the amount shows as a lump sum value of all the registration status rather than individual break down of each status.
What should I do in this case? Thank you!
I am not able to understand the requirement clearly - If you could securely share the sample data, I can try to find a way.
Hi Ceebu!
Thank you. I managed to solve the problem on linking the data thanks to you suggestion.
I have a follow up question: now that I have created a calculated field of registration count for each of the status under registration status field (approved, declined, in process). I would like to add a "submitted" status to my table, which is the sum of current 3 status. I was wondering what would be the best approach to do this?
Thanks!
Hi @zguan0923,
I'd like to suggest you write a formula to get the distinct count of the state of current item, if it equal to 3, then return "submitted".
Sample formula:
Submit State = IF(CALCULATE(DISTINCTCOUNT(Table[Registration Status]),FILTER(ALL(Table),[Opp ID]=MAX([Opp ID])))=3,"submitted","not submitted")
Regards.
Xiaoxin Sheng
Thank you Xiaoxin.
I have tried your recommended approach and received the following error msg: "too many arguments were passed to the MAX function. The maximum argument count for the function is 2."
To give you a bit more background - "Submitted" does not currently exist in the data table. I need to 1) create a submitted field as one of the options under Registration Status, and 2) calculate the the registration count for submited (sum of the other registration status - approved, denied, in process) . I hope this makes sense. Thank you!
Table 1 | Current Output | ||||
Opp ID | Registration Status | Registraion Status | Registration Count | ||
123456 | Approved | Approved | 4 | ||
234567 | Approved | Denied | 4 | ||
345678 | Approved | In Process | 2 | ||
456789 | Approved | ||||
987654 | Denied | ||||
876543 | Denied | Target Output | |||
765432 | Denied | Registraion Status | Registration Count | ||
654321 | Denied | Approved | 4 | ||
135790 | In Process | Denied | 4 | ||
975310 | In Process | In Process | 2 | ||
Submitted | 10 |
Hi @zguan0923,
You can try to use below formula to get the output table.
Output = UNION(SUMMARIZE(Sheet2,[Registration Status],"Count",COUNT(Sheet2[Registration Status])),ROW("Registration Status","Submitted","Count",COUNTROWS(Sheet2)))
Notice: As you said, current "submitted" row not real contains in the table, so I use the union and row function to add them to the merged table.
Regards,
Xiaoxin Sheng
Thank you! It's working now! Very helpful!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |