Forum Discussion
Results in custom table
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
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
2 Replies
v-piga-msftResident Rockstar
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