Forum Discussion
Create a transposed table from an existing table
I have the following table with 10 KPI but I am struggling to make a bar chart and an stacked bar chart with it, I did a manual table and found the solution that allow me to plot the data and get my chart. But I need to do it using DAX or in any automated manner so it updates automatically when refreshed.
To be able to plot the data I need to create a new table that transform this:
Into this
Could you kindly offer one solution?
Kind regards
I solved like this:
Copied the original table.
In the copied table selected the 10 KPI columns and unpivot them.
Renamed The Attribute Colum to KPI
Clicked “Create New table” in the data model and used the summarize function as follow:
SummarizedRAG =
SUMMARIZE(
'DB (3)',
'DB (3)'[KPI],
"RedCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Red")),
"AmberCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Amber")),
"GreenCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Green"))
)
6 Replies
Hello Alicito,
You can achive this using few transformation in Power query.1. Remove the Promoted Headers step from Applied steps.
2. Select all the colums and Transpose.
3.Rename 1st column to KPI.
4. Select the KPI column and click on Unpivot other Columns.
5. Select KPI & Value column and Remove Other Column.
6.Duplicate the KPI column.
7. select Value Column & Pivot it. (select KPi column as values).
You will have your desired result.
I have attached the screenshot for your referance.Thanks for your reply Jadhav, it kind of worked but the numbers dont add up, for the HSE column I should be getting 25 green and one amber.
And I am getting 34 gree and 1 amber
Hello Alicito,
You can use power query for that
1. Go to transform Data
2. Go to transform tab, Now select all three colum of your data i.e Red, Amber, Green.
3.Under "Any column" section you will find Unpivot column. click on Unpivot colum.
4. now your data will be divided to 3 three columns KPI, Attribute & Values.
5. you can rename Attribute and values column as per your need.
I hope this will be helpfull.
ChiragGarg2512Solution Sage
kiran_jadhav The problem is to change the data in first image to data in second image.
- parry2k
Super User
alicito1802 It is pretty straightforward, you don't need to unpivot and pivot data:
- transform data -> select all columns -> right click -> Unpivot columns
You will get two columns, attribute and value, rename these as you wish. close and apply
To visualize, take a matrix visual: add attribute on rows, value on columns, and count of value on value section of matrix visual and you will get the result you need.
I solved like this:
Copied the original table.
In the copied table selected the 10 KPI columns and unpivot them.
Renamed The Attribute Colum to KPI
Clicked “Create New table” in the data model and used the summarize function as follow:
SummarizedRAG =
SUMMARIZE(
'DB (3)',
'DB (3)'[KPI],
"RedCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Red")),
"AmberCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Amber")),
"GreenCount", COUNTROWS(FILTER('DB (3)','DB (3)'[Value]="Green"))
)
