Hi!
I've got a question that is fairly hard to describe (hence the topic title) and impossible to google for, so I'm forced to turn to the experts... Bear with me while I paint the picture.
First and foremost: I'm using Excel 2013.
I've got a large table, couple thousand rows by a hundred or so columns, which contains what can best be described as order lines: when, who, what, to whom, how, that sort of stuff. Every line has 5 KPI metric columns on the end, defined by formulas, and they can all be one of 5 values, like Open, SLA met, SLA not met, etc. So 5 columns, each with one of about 5 values for each row. Among the 100+ columns there are of course some that describe geographical location, some have various timestamps, and many other fields which would be useful to filter by. The end goal of my efforts is to create a sort-of scorecard which can be used to analyze this data, slice and group the KPIs in different ways, and the aggregation method would be to count the distinct Row IDs (a column in the data) using any given grouping of the data.
Something like this, in a nutshell:
[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]Row ID[/TD]
[TD]Country[/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]KPI1[/TD]
[TD]KPI2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]US[/TD]
[TD]2017[/TD]
[TD]01[/TD]
[TD]Met[/TD]
[TD]Met[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]US[/TD]
[TD]2017[/TD]
[TD]01[/TD]
[TD]Not Met[/TD]
[TD]Met[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GER[/TD]
[TD]2017[/TD]
[TD]02[/TD]
[TD]Met[/TD]
[TD]Not Met[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]UK[/TD]
[TD]2016[/TD]
[TD]12[/TD]
[TD]Met[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
One way to do this, which gets basic results but is static, is to import the table into the Data Model, create one pivot table for every KPI metric by pulling it into either the rows or columns field, and as values selecting the Distinct Count aggregation method on the Row ID field. The problem with this is that all the relevant data is spread over 5 different pivot tables which can't all be modified together if I, say, want to change the grouping I use.
So I thought "There must be a way to create one measure per KPI in PowerPivot, and then I can just use them as the Values with only the actual grouping in the rows and columns! All I need is a measure that somehow takes the Row/Column filter context (i.e. whatever the last filter in the Pivot Table is) and filters only the specific KPI field, then counts the distinct Row IDs." Question is: how?
One way which does work, but is pretty awkward, is to create one measure per KPI value, not field: =CALCULATE(DISTINCTCOUNT(Table[Row ID]), Table[KPI1]="Met"), =CALCULATE(DISTINCTCOUNT(Table[Row ID]), Table[KPI1]="Not Met"), =CALCULATE(DISTINCTCOUNT(Table[Row ID]), Table[KPI2]="Met"), etc.
Then I can just drag all these to the Values section and I'm done. Problem is, if I want to change the KPI functions to return, say, "Pass" instead of "Met", I have to change the measures too... So is there a way to create just ONE measure per KPI which will do the trick? To effectively make the "Met", "Not Met", etc. part of those measures above dynamic?
As I thought about the problem I kept going back and forth between "Surely this must be possible" and "Does this even make sense? Can it even be done conceptually?", so I won't be too surprised if the straightforward solution above ends up being the only one, but my gut tells me that with some combination of CALCULATE and FILTER and ALL it might be possible. In any case, I hope someone here will be able to put the issue to rest one way or another.
Thanks in advance!
I've got a question that is fairly hard to describe (hence the topic title) and impossible to google for, so I'm forced to turn to the experts... Bear with me while I paint the picture.
First and foremost: I'm using Excel 2013.
I've got a large table, couple thousand rows by a hundred or so columns, which contains what can best be described as order lines: when, who, what, to whom, how, that sort of stuff. Every line has 5 KPI metric columns on the end, defined by formulas, and they can all be one of 5 values, like Open, SLA met, SLA not met, etc. So 5 columns, each with one of about 5 values for each row. Among the 100+ columns there are of course some that describe geographical location, some have various timestamps, and many other fields which would be useful to filter by. The end goal of my efforts is to create a sort-of scorecard which can be used to analyze this data, slice and group the KPIs in different ways, and the aggregation method would be to count the distinct Row IDs (a column in the data) using any given grouping of the data.
Something like this, in a nutshell:
[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]Row ID[/TD]
[TD]Country[/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]KPI1[/TD]
[TD]KPI2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]US[/TD]
[TD]2017[/TD]
[TD]01[/TD]
[TD]Met[/TD]
[TD]Met[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]US[/TD]
[TD]2017[/TD]
[TD]01[/TD]
[TD]Not Met[/TD]
[TD]Met[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GER[/TD]
[TD]2017[/TD]
[TD]02[/TD]
[TD]Met[/TD]
[TD]Not Met[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]UK[/TD]
[TD]2016[/TD]
[TD]12[/TD]
[TD]Met[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
One way to do this, which gets basic results but is static, is to import the table into the Data Model, create one pivot table for every KPI metric by pulling it into either the rows or columns field, and as values selecting the Distinct Count aggregation method on the Row ID field. The problem with this is that all the relevant data is spread over 5 different pivot tables which can't all be modified together if I, say, want to change the grouping I use.
So I thought "There must be a way to create one measure per KPI in PowerPivot, and then I can just use them as the Values with only the actual grouping in the rows and columns! All I need is a measure that somehow takes the Row/Column filter context (i.e. whatever the last filter in the Pivot Table is) and filters only the specific KPI field, then counts the distinct Row IDs." Question is: how?
One way which does work, but is pretty awkward, is to create one measure per KPI value, not field: =CALCULATE(DISTINCTCOUNT(Table[Row ID]), Table[KPI1]="Met"), =CALCULATE(DISTINCTCOUNT(Table[Row ID]), Table[KPI1]="Not Met"), =CALCULATE(DISTINCTCOUNT(Table[Row ID]), Table[KPI2]="Met"), etc.
Then I can just drag all these to the Values section and I'm done. Problem is, if I want to change the KPI functions to return, say, "Pass" instead of "Met", I have to change the measures too... So is there a way to create just ONE measure per KPI which will do the trick? To effectively make the "Met", "Not Met", etc. part of those measures above dynamic?
As I thought about the problem I kept going back and forth between "Surely this must be possible" and "Does this even make sense? Can it even be done conceptually?", so I won't be too surprised if the straightforward solution above ends up being the only one, but my gut tells me that with some combination of CALCULATE and FILTER and ALL it might be possible. In any case, I hope someone here will be able to put the issue to rest one way or another.
Thanks in advance!
