Hi All,
I have a table of different KPI metrics collected across teams in excel files.
Each KPI may be measured in different units. Hence the Targets are in % or scale of 1 to 5, or simple an absolute number, etc. The sample data and format of raw data collection table is as follows:
LIke this I have data for multiple client names, Metrics, and for multiple months from multiple individual files. The data goes in 1000s of rows, with different units like $, %, Number, Ratings, Hours, etc.
I need to consolidate and represent the above information in some PowerBI based dashboards. One of the section in the dashboard needs to be in tabular format as follows:
I need to reformat or transpose this data in this format:
My team is able to achieve all of it, except one challenge. the rows with % format is displaying in plain values in decimals. ! How can I depict the info without losing its format.
I used the consolidated file and created Pivot table in excel with above format. The challenge I continue to face is the rows with % values are dropping the % format and shows plain values.
Example 1: the overall actual in values section of Pivot table is showing 90% as 0.90. If i change the format, it will change the format for those rows which have to be plain number.
Example 2: The Target column is added under Rows section of Pivot table, where the % is again showing as plain values, and I cannot even change the number format.
Request to guide me on how to transform and represent with format intact.
Kindly suggest any suitable solution please.
Regards,
Prasad DN
I have a table of different KPI metrics collected across teams in excel files.
Each KPI may be measured in different units. Hence the Targets are in % or scale of 1 to 5, or simple an absolute number, etc. The sample data and format of raw data collection table is as follows:
Client Name | Metric Name | Metric Definition | Unit of Metrics | Target | Start Date | End date | Numerator | Denominator | Overall Actual | Metric Status |
ABC | Accuracy | some text | % | 95% | 1 Jan 23 | 31 Jan 23 | 900 | 1000 | 90% | Not Met |
ABC | CSAT | some text | 1 to 5 scale | 4 | 1 Jan 23 | 31 Jan 23 | NA | NA | 4.1 | Met |
ABC | No of Issues post go-live | some text | Number | 0 | 1 Jan 23 | 31 Jan 23 | NA | NA | 1 | Not Met |
ABC | Accuracy | some text | % | 95% | 1 Feb 23 | 1 Feb 23 | 950 | 1000 | 95% | Met |
ABC | CSAT | some text | 1 to 5 scale | 4 | 1 Feb 23 | 1 Feb 23 | NA | NA | 4.5 | Met |
ABC | No of Issues post go-live | some text | Number | 0 | 1 Feb 23 | 1 Feb 23 | NA | NA | 0 | Met |
LIke this I have data for multiple client names, Metrics, and for multiple months from multiple individual files. The data goes in 1000s of rows, with different units like $, %, Number, Ratings, Hours, etc.
I need to consolidate and represent the above information in some PowerBI based dashboards. One of the section in the dashboard needs to be in tabular format as follows:
I need to reformat or transpose this data in this format:
Client Name | Metric Name | Metric Def | Target | Jan 23 | Feb 23 | Mar 23 | and so on Dec 23 |
ABC | Accuracy | some text | 95% | 90% | 95% | and so on... | |
ABC | CSAT | some text | 4 | 4.1 | 4.5 | ||
ABC | No of issues post go-live | some text | 0 | 1 | 0 |
My team is able to achieve all of it, except one challenge. the rows with % format is displaying in plain values in decimals. ! How can I depict the info without losing its format.
I used the consolidated file and created Pivot table in excel with above format. The challenge I continue to face is the rows with % values are dropping the % format and shows plain values.
Example 1: the overall actual in values section of Pivot table is showing 90% as 0.90. If i change the format, it will change the format for those rows which have to be plain number.
Example 2: The Target column is added under Rows section of Pivot table, where the % is again showing as plain values, and I cannot even change the number format.
Request to guide me on how to transform and represent with format intact.
Kindly suggest any suitable solution please.
Regards,
Prasad DN