You mention that "the metric values are calculated in a Parameter"; but calculations actually take place outside parameters. Calculations are typically created using the parameter selection.
Do you have one field called "METRIC" or one field for each metric? Are your scorecard items in separate fields (one field called Score, another Rating, another Rating Value, another Metric Weight) or are they created by using just two fields (where one is your Scorecard name and the other is the scorecard value)?
Would you be able to post a packaged workbook of what you're working with? Seeing how things are set up and the interactions you have in place will likely lead to a more timely and accurate response.
Ok so the metric parameter isn't calculated but what I meant is this is how the metric selections are listed. Once selected the metrics are calculated. The user can select filters to display the needed graph. The scorecard is a new sheet that will produce the example I included. It will read the existing database and create all the fields on the scorecard for each metric row. The calculations are:
- Metric - Metric name
- Score - Calculation for the metric for ytd to the month selected.
- Rating - A calculation that takes the Score and compares it to a set of ranges to determine and display a value of "Satisfactory" or "Unsatisfactory".
- Rating Value - "1" or "2" numeric value determined by the Rating value to used to multiply by the Metric Weight to come up with the Total.
Each row/column value will be calculated each time the selected month is selected. First, is I don't how to create the field locations on the sheet. I would imagine it has to be a table creation? Second, is how to create the first selection of the metric values. The Metric Report parameter is the only place the metrics are listed.
This is something that I've never done and I'm having a hard time wrapping my head around on how it should/can be done. I've attached an example.
Example.twbx.zip 61.4 KB
1 of 1 people found this helpful
Based on what you have, it appears that you are trying to group a set of measure column headings and treat them as a column of dimension values. You would need to be able to reshape the data in such a way that each measure column heading is a dimension value instead of its own column.
Currently you have something similar to:
Application Date Availability Mean Time to Recover Mean to to Failure App1 01/01/01 .92 10 2 App2 01/01/01 .93 12 3
and to do the table you're talking about, you would need something like:
Application Date Metric Metric Value App1 01/01/01 Availability .92 App1 01/01/01 Mean Time to Recover 10 App1 01/01/01 Mean Time to Failure 2 App2 01/01/01 Availability .93 App2 01/01/01 Mean Time to Recover 12 App2 01/01/01 Mean Time to Failure 3
You may be able to get what you're looking for by creating an Excel table that includes a METRIC column that has the list of values from your parameter. A second column would be needed to join the existing table to this new table (I chose App Owner - as that would produce a smaller number of needed records in the new Excel table than the Application field). One set of Metric values per each App Owner (7 Metrics, 5 App Owners, 35 records in new table).
I then brought in the new table and blended the two tables. After creating a few calculated fields and making some assumptions on how you are doing things, I have a table similar to what you were wanting (minus the bottom right-hand corner of the Excel table posted in your initial email).
I've attached the Excel file I used along with the workbook I was able to generate with it and your current workbook.
Hope this helps,
Thanks for the reply. You definetly got me closer the end result. I've played with your solution and noticed my Primary Data Source is now my Secondary and I've tried to switch it but it appears this is the only way it will work. I don't think it has any effect on my other sheets.
Now I just need to figure out how to incorporate the monty selection and how to represent the Grand Total and Overall Rating. Again, thanks for getting me started. I appreciate it.