• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transpose with Number Formatting In PowerBI Dashboard- help

prasaddn

Active Member
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:
Client NameMetric NameMetric DefinitionUnit of MetricsTargetStart DateEnd dateNumeratorDenominatorOverall ActualMetric Status
ABCAccuracysome text%95%1 Jan 2331 Jan 23900100090%Not Met
ABCCSATsome text1 to 5 scale41 Jan 2331 Jan 23NANA4.1Met
ABCNo of Issues post go-livesome textNumber01 Jan 2331 Jan 23NANA1Not Met
ABCAccuracysome text%95%1 Feb 231 Feb 23950100095%Met
ABCCSATsome text1 to 5 scale4
1 Feb 23
1 Feb 23NANA4.5Met
ABCNo of Issues post go-livesome textNumber01 Feb 231 Feb 23NANA0Met

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 NameMetric NameMetric DefTargetJan 23Feb 23Mar 23and so on Dec 23
ABCAccuracysome text95%90%95%and so on...
ABCCSATsome text44.14.5
ABCNo of issues post go-livesome text010

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
 

prasaddn

Why did You duplicate Your thread?
You' ve opened already

Now You opened it still under Ask an Excel Question ... and You're looking for Power BI ... -solution.
Please, reread Forum Rules
This thread has moved in this time in correct Forum
and You previous thread has closed.
 
Back
Top