Using the same measure for different columns of a table

[Anonymous]

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:

# qualified phase 1 = CALCULATE( DISTINCTCOUNT('Table'[id]), FILTER('Table', [Current phase] > 1))
# qualified phase 1 = CALCULATE(DISTINCTCOUNT('Table'[id]), FILTER('Table)', 'Table'[Phase 1] = "qualified"))

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!

[v-zhangti · Accepted 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.

[Rupak_bi]

I think summarizetable will solve the purpose