Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello, I have a data table that contains information about testing produced items. After an item is produced, it needs to go through several test phases. An item being in a phase means it has ben qualified at the previous phases, so if [Current phase] = 3, it means it is qualified at phases 1 and 2. My data table looks like this:
ID | Production date | status | Current phase | Phase 1 | Phase 2 | Phase 3 | Phase 4 | Phase 5 |
1 | 1-1-2023 | qualified | 2 | qualified | waiting for results | |||
2 | 2-1-2023 | qualified | 2 | qualified | waiting for results | |||
3 | 3-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
4 | 4-1-2023 | qualified | 2 | qualified | waiting for results | |||
5 | 5-1-2023 | qualified | 2 | qualified | waiting for results | |||
6 | 6-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
7 | 7-1-2023 | disqualified | 4 | qualified | qualified | qualified | disqualified | |
8 | 8-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
9 | 9-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
10 | 10-1-2023 | qualified | 5 | qualified | qualified | qualified | qualified | qualified |
11 | 11-1-2023 | qualified | 2 | qualified | waiting for results | |||
12 | 12-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
13 | 13-1-2023 | disqualified | 2 | qualified | disqualified | |||
14 | 14-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
15 | 15-1-2023 | disqualified | 1 | disqualified | ||||
16 | 16-1-2023 | qualified | 5 | qualified | qualified | qualified | qualified | qualified |
17 | 17-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
18 | 18-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
19 | 19-1-2023 | qualified | 2 | qualified | waiting for results | |||
20 | 20-1-2023 | qualified | 4 | qualified | qualified | qualified | waiting for results | |
21 | 21-1-2023 | qualified | 5 | qualified | qualified | qualified | qualified | waiting for results |
22 | 22-1-2023 | disqualified | 2 | qualified | disqualified | |||
23 | 23-1-2023 | qualified | 3 | qualified | qualified | waiting for results | ||
24 | 24-1-2023 | qualified | 5 | qualified | qualified | qualified | qualified | qualified |
25 | 25-1-2023 | qualified | 4 | qualified | qualified | qualified | waiting for results | |
26 | 26-1-2023 | disqualified | 2 | qualified | disqualified | |||
27 | 27-1-2023 | disqualified | 1 | disqualified | ||||
28 | 28-1-2023 | disqualified | 2 | qualified | disqualified | |||
29 | 29-1-2023 | disqualified | 2 | qualified | disqualified | |||
30 | 30-1-2023 | disqualified | 5 | qualified | qualified | qualified | qualified | disqualified |
To analyze this, I want to calculate the percentage qualified at each phase. My resulting table should look like this:
Phase 1 | Phase 2 | Phase 3 | Phase 4 | Phase 5 | |
qualified | 28 | 17 | 8 | 5 | 3 |
disqualified | 2 | 5 | 0 | 1 | 1 |
waiting for results | 0 | 6 | 9 | 2 | 1 |
Percentage qualified | 93% | 61% | 47% | 63% | 60% |
I have created a measure in two different ways to calculate the number of qualified items of the first phase:
These measures both work, however, I would need to create separate measures for each of the phases, and in reality, there are numerous more phases than displayed in this example of the data. I have tried to create a new table with these success rates, but this won't allow me to drill down the data by dates.
Any ideas on how to create one measure for each of the phases or any other suggestions would be very much appreciated!
Solved! Go to Solution.
Hi, @Anonymous
You can try the following methods.
In Power Query, select the first four columns, and then select the Unpivot Other Columns:
Result:
Measure:
Measure =
Var _count1=DISTINCTCOUNT('Table'[ID])
Var _countall=CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),[Value]<>BLANK()))+0
Var _countqualified=CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Attribute]=SELECTEDVALUE('Table'[Attribute])&&[Value]="qualified"))
Return
IF(HASONEVALUE('Table'[Value]),_count1,DIVIDE(_countqualified,_countall))
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
You can try the following methods.
In Power Query, select the first four columns, and then select the Unpivot Other Columns:
Result:
Measure:
Measure =
Var _count1=DISTINCTCOUNT('Table'[ID])
Var _countall=CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),[Value]<>BLANK()))+0
Var _countqualified=CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Attribute]=SELECTEDVALUE('Table'[Attribute])&&[Value]="qualified"))
Return
IF(HASONEVALUE('Table'[Value]),_count1,DIVIDE(_countqualified,_countall))
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I think summarizetable will solve the purpose
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
18 | |
17 |
User | Count |
---|---|
37 | |
22 | |
19 | |
18 | |
13 |