Simple Drill Down

[Anonymous]

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

[Anonymous]

may I ask if I would like to return the score as it is selected, instead of the average. is there a function to do that? thank you.

[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.

[Anonymous]

understood. thanks again! 

[Anonymous]

Thanks, a million! you are a great saviour 🙂 

[daxer-almighty · Accepted solution]

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