Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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.
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.
understood. thanks again!
Thanks, a million! you are a great saviour 🙂
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |