Average in sunburst chart

Question

I'm creating a sunburst chart Microsoft provided (v2.6.0.0). Each value is calculated as the sum of the values of the previous level in hiearchy. Is it possible to calculate it based on the average?

Using the example Microsoft provided (with a little edit from my side):

Category	Subcategory	Area	Sales
Category1	SC1	Asia	5
Category1	SC1	Europe	1
Category1	SC1	North America	9
Category1	SC2	Asia	6
Category1	SC2	Europe	2
Category2	SC3	Asia	7
Category2	SC3	Europe	3
Category2	SC3	North America	11
Category2	SC4	Asia	8
Category2	SC4	Europe	4
Category2	SC4	North America	12

I need the sales value of SC1 to be 5, not 15. And SC2 needs to be 4, not 8.
Furthermore, I need the sales value of Category1 to be 4,5 (the average of SC1 and SC2), not 5 (the average of the 5 areas).

I can't seem to get the table in the right format, I hope it's readable.

anonymous · Answer

Hi&nbsp;Giten&nbsp;,
Thank you for your kind reply!
The same goes for us, I will contact you if there is any follow up, and if you find a solution to the problem, please share it here as well, it will help more people!
Thank you for your understanding!
Best regards,
Lucy Chen

giten · Answer

Thanks! If I find a solution, I will post it here.

ryan_mayu · Answer

I tried this chart. SC1 level can use average. However, the higher level will sum up automatically.&nbsp;

anonymous · Answer

Hi&nbsp;Giten&nbsp;,
Please create a new measure.
Subcategory_Avg_Sales = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Category],
        'Table'[Subcategory],
        "AverageSales",
        AVERAGE('Table'[Sales])
    ),
    [AverageSales]
)
Please check if this is the output you are expecting。

Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

giten · Answer

Hi&nbsp;Lucy Chen,Thanks! The table shows the right average (also for Category1 and Category2). However, if I put it in a sunburst, the subcategories and categories are still summed up. I guess that's what&nbsp;ryan_mayu&nbsp;meant.&nbsp;P.S. I'm new here, how do I attach a pbix?