Results in custom table

[Anonymous]

Hi guys,

I have source table in following format:

ID	Start Time	End Time	Time BTW	KPI	KPI5	KPI10	KPI15	KPI30	Type
1	01.01.2018 07:00:00	03.01.2018 18:14:12	2,47	KPI5	1	1	1	1	G
2	01.01.2018 07:36:02	08.01.2018 11:12:48	7,15	KPI10	0	1	1	1	G
3	01.01.2018 07:24:00	28.01.2018 11:29:00	27,17	KPI30	0	0	0	1	G
4	01.01.2018 07:10:58	12.01.2018 10:09:04	11,12	KPI15	0	0	1	1	E
5	01.01.2018 07:12:13	04.01.2018 08:59:20	3,07	KPI5	1	1	1	1	E

What I need to do is create following output table:

	Team	Type = G
	#	%	#	%
KPI5	2	40%	1	33%
KPI10	3	60%	2	67%
KPI15	4	80%	3	100%
KPI30	5	100%	3	100%

Logic: If I fulfill KPI10, then I automatically fulfill also KPI5, etc.

Logic: % KPI 10 = 3 / 5, etc.

Is there some way how to get such result - combine these data into 1 custom table?

Thanks in advance

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Do you want to get the output below?

If it is, you may do some changes for your data model in Query Editor.

1. Select the columns KPI5,KPI10,KPI15,KPI30 and Click Unpivot columns.

2. Change the Attribute to KPI, then Apply&& Close.

3. Create the four measures with the formula below.

Team = CALCULATE(COUNTROWS(Table1),FILTER(ALLEXCEPT(Table1,'Table1'[KPI]),'Table1'[Value]=1))

% = DIVIDE('Table1'[Team],CALCULATE(COUNT(Table1[Value])))

type_G = CALCULATE(COUNTROWS('Table1'),FILTER(ALLEXCEPT(Table1,Table1[KPI]),'Table1'[Value]=1&&'Table1'[Type]="G"))

%_ = DIVIDE([type_G], CALCULATE(COUNTROWS('Table1'),FILTER('Table1','Table1'[Type]="G")))

More details, you could have a reference of the attachment.

Best  Regards,

Cherry

[Anonymous]

Thank you so much! Your solution works perfectly 🙂