Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |