cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slowlearner Frequent Visitor
Frequent Visitor

I have a data of following type. Could anyone please help me make chart as the below.

I am new to power BI and recently started making some dashboards reports.

 

I have a table as below.

 

NameScore AScore BScore CScore D
College A7443
College A3364
College A5585
College B2686
College B6322
College C2548
College C4324
College D8463
College D6774
College D2482

 

Can any one help me summarize above table as 

 

NameAverage Score AAverage Score BAverage Score CAverage Score D
College A5464
College B4554
College C3436
College D8573
Instution5.004.505.254.25

  

in power bi and make a chart as follows.

 

 Capture.PNG

 

I could do it in excel but not in power BI.

1 ACCEPTED SOLUTION

Accepted Solutions
DaFloDo Regular Visitor
Regular Visitor

Re: I have a data of following type. Could anyone please help me make chart as the below.

hi @slowlearner,

 

alternatively to the suggested DAX way you could use power query when getting the data into power bi so it fits your needs.

I made an pbix showing the transformations step by step - you would do it in a single step, so you do not end up with the intermediate steps.

 

https://www.dropbox.com/s/5fugnzrsvpwr9er/unpivot%20college%20chart.pbix?dl=0

 

 

2018-09-24 18_31_57-unpivot college chart - Power BI Desktop.png

steps were:

- 00 base data as you provided

- 01 aggregating data (00) to institution level (average for scores)

- 02 unpivot the score columns

- 03 unpivoting the base data (00)

- 04 union of (02) and (03)

 

 

 

 

best regards

 

florian

 

6 REPLIES 6
rajendran Super Contributor
Super Contributor

Re: I have a data of following type. Could anyone please help me make chart as the below.

Hi @slowlearner

 

You can create a calculated table using the below DAX ( Go to Modelling tab -> New Table, use this code)

 

Avg summary = SUMMARIZE('Avg',
ROLLUP('Avg'[Name]),
"Avg of A", AVERAGE('Avg'[Score A]),
"Avg of B", AVERAGE('Avg'[Score B]),
"Avg of C", AVERAGE('Avg'[Score C]),
"Avg of D", AVERAGE('Avg'[Score D])
)

 

2. Create a new column

Rename = IF(ISBLANK('Avg summary'[Name]),"Institution", 'Avg summary'[Name])

Use "stackd bar chart" visual to get your desired result.

 

 

Grand Average.PNG 

 

Thanks

Raj

slowlearner Frequent Visitor
Frequent Visitor

Re: I have a data of following type. Could anyone please help me make chart as the below.

Thank you for the reply.

I need college names as an legend and average of A, B, C,D as an axis.

 

DaFloDo Regular Visitor
Regular Visitor

Re: I have a data of following type. Could anyone please help me make chart as the below.

hi @slowlearner,

 

alternatively to the suggested DAX way you could use power query when getting the data into power bi so it fits your needs.

I made an pbix showing the transformations step by step - you would do it in a single step, so you do not end up with the intermediate steps.

 

https://www.dropbox.com/s/5fugnzrsvpwr9er/unpivot%20college%20chart.pbix?dl=0

 

 

2018-09-24 18_31_57-unpivot college chart - Power BI Desktop.png

steps were:

- 00 base data as you provided

- 01 aggregating data (00) to institution level (average for scores)

- 02 unpivot the score columns

- 03 unpivoting the base data (00)

- 04 union of (02) and (03)

 

 

 

 

best regards

 

florian

 

Highlighted
rajendran Super Contributor
Super Contributor

Re: I have a data of following type. Could anyone please help me make chart as the below.

Hi @slowlearner

 

To get the Score as Axis, you need to do this in Power Query and use Pivot as suggested by @DaFloDo .

 

Thanks

Raj

slowlearner Frequent Visitor
Frequent Visitor

Re: I have a data of following type. Could anyone please help me make chart as the below.

Hi @DaFloDo,

Thank you for your reply.

Do I have to manually do the step 02 and 03?

How do I automatically unpivot?

 

Thanks,

Santo

DaFloDo Regular Visitor
Regular Visitor

Re: I have a data of following type. Could anyone please help me make chart as the below.

Hi @slowlearner,

 

you perform these steps only one time when you define your query. I would do this manually using the UI. Alternatively you can use the Power Query M code provided within the sample file as a starting point.

After that, each time you refresh the data (for example after the source has been updated) these steps are automatically performed to bring the data into your Power BI model.

 

best regards 

 

Florian

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 209 members 2,333 guests
Please welcome our newest community members: