Hierarchies in Tables

[valleyman]

I am still learning how to use PBI, so apologies.

Let's say, as an example, I have a table that shows sales quantities for salesmen and they work in different departments. The table is effectively as follows:

Name, Department, Number Sold

I have created a standard, stacked column chart with Department and Name as Axis, and Number Sold for the Values. I would like to have the chart show the AVERAGE Number Sold when looking at the Department level of the hierarchy, and then show the INDIVIDUAL Number sold when clicking the down-arrow option to drill down for each member of that department. Is this possible? Any advice would be gratefully received. 

Thanks

[valleyman · Accepted solution]

Please ignore me, I was an idiot. I assumed that if I selected Average for Numbers Sold at the top level, that it would somehow skew the individual numbers when I drilled down. It doesn't. I changed it to Average from Sum at the top level, which shows me the average Numbers Sold for all the departments. Then, when I drilled down, it showed me the individual people. 

Thanks to everyone for your assistance, though!

[amitchandak]

@valleyman , Not very clear you can use matrix visual

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual
https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

https://www.tutorialgateway.org/create-hierarchy-in-power-bi/

[Adescrit]

HI @valleyman 

You could build this functionality into the DAX used to create the measure in the stacked column chart.

For example: 

Average Number Sold Per Seller = 

    SUM(Table[Number Sold]) / DISTINCTCOUNT(Table[Name])

When you drill down into a particular seller, the distinct count will be 1 do the measure will return the total sales for that seller.

Hopefully this works but please let me know.