Power BI: Include Total Average on the chart.

[alfranco17]

Hi.

I have to chart the results of a poll. The poll groups questions by categories. For instance: the Dessert category includes Ice Cream, and the Flavors question includes Vanilla, Chocolate and so on.

I need to include the total average for the category in the chart. This is an example (the example comes from Excel, because there I can copy the pivot table as links and then chart it). Is it possible to get this with Power BI?

This is the data I used.

Name	Dessert	Flavor	Rating
Patricia	Ice cream	Vanilla	1
Robert	Ice cream	Vanilla	4
Richard	Ice cream	Vanilla	4
Karen	Ice cream	Vanilla	3
William	Ice cream	Vanilla	3
Daniel	Ice cream	Vanilla	3
Jessica	Ice cream	Vanilla	1
Patricia	Ice cream	Cookies and cream	1
Robert	Ice cream	Cookies and cream	3
Richard	Ice cream	Cookies and cream	2
Karen	Ice cream	Cookies and cream	4
William	Ice cream	Cookies and cream	5
Daniel	Ice cream	Cookies and cream	5
Jessica	Ice cream	Cookies and cream	3
Patricia	Ice cream	Chocolate	5
Robert	Ice cream	Chocolate	4
Richard	Ice cream	Chocolate	3
Karen	Ice cream	Chocolate	3
William	Ice cream	Chocolate	1
Daniel	Ice cream	Chocolate	4
Jessica	Ice cream	Chocolate	3

<tbody>
</tbody>

Thanks!
Armando

 

[Matt Allington]

I’m sure it can be done, but it may take some setup. As an alternative, Howe about you do a column/line combo chart, and put the category total as a line? Would that work? You would need a measure that returns the category total. The formula depends on your data model, but something like =calculate([average measure],all(table[flavour]))

 

[alfranco17]

Thanks, Matt.

I tried out your suggestion. When I saw it,I thought: "That line is not what I need. I need to append that measure at the bottom...". And then, your "it may take some setup"...

And then it hit me. I could use Power Query to pivot two tables:
* one for totals by dessert, year and flavor (yes, there's also a year involved),
* another by dessert and year.

Then create an append query, and use that table in the data model.

This is the result:

I think it is a lot of work, though, and hope there is another way to do it.

 

[Matt Allington]

The tools are not really designed for the scenario you describe. I understand what you want, and even why (although I still think the line is good - JMO), but the tools don't really work that way. Either you need the "Total" data as a dimension (like you have done with Power Query) or you need to hard code measures for every item in the list. I can imagine you may be able to hack a solution by creating another dimension table with phantom total lines. I have it in my head and can't really explain it. But if you are interested in where I am going with this you could watch this video. https://www.youtube.com/watch?v=ojHZkWkEY7Q It is not the same thing, but I am thinking the concept could be modified to handle your use case. Let me know if you think this is interesting. No promises, but if it is really important I may take a look (if and when I get time), and then write a blog

 

[alfranco17]

Thanks! I am watching the video right now. There's no hurry, I'd love to read it if you write it.

Best regards.
Armando.