Average in sunburst chart

[Giten]

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]

Hi @Giten ,

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]

Hi 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 @ryan_mayu meant.

P.S. I'm new here, how do I attach a pbix?

[Anonymous]

Hi @Giten ,

Thank you for your kind reply!

I apologize for using the wrong visualization.

I made a lot of attempts. It's true that he can't calculate the average of the previous level (Sunburst), but I found another visualization.

Although he still can't show the averages of the categories, we can view the averages of the different categories in a table.

In addition, you need to be a super user to attach files on messages.

The other option is to put your files in a dropbox, onedrive or googledrive folder and share the link with the community.

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]

Thanks for all the effort, this helps a bit. Still, I'm looking for averages in the sunburst itself, but maybe that's just not possible. I'll keep looking.

[Anonymous]

Hi @Giten ,

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]

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

[Anonymous]

Hi @Giten ,

Once you've shared your solution, please accept your own reply as the answer! It will make it easier for other users who may be experiencing the same problem to find a solution.

Thank you for your understanding!

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.

[ryan_mayu]

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