Create a transposed table from an existing table

[alicito1802]

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

[parry2k]

@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.

[alicito1802 · Accepted solution]

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"))

)

[kiran_jadhav]

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.

 

 

 

 

  

 

[alicito1802]

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

[kiran_jadhav]

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. 

[ChiragGarg2512]

@kiran_jadhav The problem is to change the data in first image to data in second image.