Forum Discussion
Simple Drill Down
Hi
I know this may be a simple task, however, I have spend a few days trying to figure out how to allow drill down with a sample set of data that I have.
I have a set of application , and their compliance score per category; where overall will branch to Application and OS and then OS branch to windows and Linux. How do i go about creating a matrix/pivot that will show the ApplicationName at top level with the overall compliance score, allowing user to drill down to application and os, and then from os to windows and linux?
any help is greatly appreciated. thank you!
| ApplicationName | SLA_Type | PercentageCompliance |
| MondayApp | OS-Linux | 38.54 |
| MondayApp | OS | 38.54 |
| MondayApp | Application | 26.67 |
| MondayApp | Overall | 36.94 |
| TuesdayApp | OS-Windows | 39.73 |
| TuesdayApp | OS | 39.73 |
| TuesdayApp | Application | 45.45 |
| TuesdayApp | Overall | 41.05 |
| FunApp | OS-Windows | 50.9 |
| FunApp | OS | 50.9 |
| FunApp | Application | 35.37 |
| FunApp | Overall | 48.19 |
| App123 | OS-Windows | 64.29 |
| App123 | OS | 64.29 |
| App123 | Application | 33.33 |
| App123 | Overall | 58.82 |
| App365 | OS-Windows | 70.34 |
| App365 | OS | 70.34 |
| App365 | Application | 40.48 |
| App365 | Overall | 63.64 |
You clearly have hierarchies here, so you have to create them. Bear in mind that hierarchies must reside in one and the same table. When designing tables with hierarchies, think about what a Date table looks like... Once you get your hierarchy/hierarchies right, you're almost done with your task.
Here's a file that does what you want:
https://1drv.ms/u/s!ApyQEauTSLtOgZlNkcwZPyDsKLJojw?e=iWH3bv
5 Replies
- daxer-almighty
Solution Sage
You clearly have hierarchies here, so you have to create them. Bear in mind that hierarchies must reside in one and the same table. When designing tables with hierarchies, think about what a Date table looks like... Once you get your hierarchy/hierarchies right, you're almost done with your task.
Here's a file that does what you want:
https://1drv.ms/u/s!ApyQEauTSLtOgZlNkcwZPyDsKLJojw?e=iWH3bv
- daxer-almighty
Anonymous
I don't really understand what you mean. If you select just the right entities from your tables, the average will return the raw score since it won't have to average over many items. But this is a measure, so it has to work for any context. Hence the aggregation. An average makes sense. Other aggregations not so much. If you want to check if there is just one entity in the context, then you'll have to deal with detecting context. You can use functions such as HASONEVALUE, ISINSCOPE, HASONEFILTER... But such a measure will not be universal.