Need help on pie chart

[Snapdrag]

I need help regarding Pie chart and table visual in Power BI. 

I have a 5 device utilization data for last 6 months at daily level. Now the requirement is to create a pie chart and show the colors on basis of averge utilization of the devices at month level if it falls in which category(Red, Amber, Green). 

Condition is as follow If(Average < 50, "green", Average > 50 && < 80, "amber" , Average > 80, "red")

If user select red color from pie chart then user can see the list of devices falling under red category in table visual. It should be interactive.

Device	Date	Utilization
Device 1	2024-12-20	98
Device 1	2024-12-19	52
Device 1	2024-11-13	25
Device 1	2024-11-13	70
Device 2	2024-12-20	80
Device 2	2024-12-19	20
Device 2	2024-11-13	60
Device 2	2024-11-13	10
Device 3	2024-12-20	10
Device 3	2024-12-19	90
Device 3	2024-11-13	20
Device 3	2024-11-13	50 The data is at daily level so device can under multiple categories in a single month so I tried a column but that do not show the correct count of devices in pie chart. And we can

[v-xinc-msft · Accepted solution]

Thanks for the reply from Kedar_Pande and pcoley , your replies are all fantastic! please allow me to provide some additional insights

Hi @Snapdrag ,

In Power BI, slice colours for pie charts do not currently support direct conditional formatting. However, you could achieve a similar effect with some workarounds.

1. Create a measure to calculate the average of each device.

AverageUtilization = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Device],
        'Table'[Month],
        "AvgUtilization", AVERAGE('Table'[Utilization])
    ),
    [AvgUtilization]
)

2. Create a measure to determine the colour of each slice. 

Device color = 
SWITCH(
    TRUE(),
    'Table'[AverageProduction] < 50, "green",
    'Table'[AverageProduction] >50 && 'Table'[AverageProduction] < 80, "grey",
    "Red"
)

3. Apply this colour measure in a bar chart or table.

4. Convert a bar chart or table to a pie chart. When I select green from the pie chart, I can see a list of devices belonging to the green category in the table visual object

Although pie charts themselves do not support conditional formatting, you can achieve a similar effect indirectly through this method.

Please refer to Power BI: Conditional formatting the Pie Visual - Microsoft Fabric Community

Could you please provide example data or sample files here if you have any confused? We could offer you more help if we have information in detail. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

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.

[pcoley]

@Snapdrag 
As I know, you cannot dynamically format category colors in a pie chart.
But you can dynamically format its labels.

If you have any more information please share we us, so we can try to help solving your requirement.
Please be precise of the average you want to handle: what do you mean by " basis of averge utilization of the devices at month level"? Are you planning to have 30 slices in a piechart(5 devices x 6months) or do you want to show one average (the Average of the 6-Month-Averages utilization) for each device? Do you have any column in a dimension table with dates (and a month-yy column on it)? Can you share the pbix?


I Hope this help, if so please mark it as solution. kudos are welcome😀

[Kedar_Pande]

@Snapdrag 

Create a measure

Avg Utilization = 
CALCULATE(
AVERAGE('Table'[Utilization]),
ALLEXCEPT('Table', 'Table'[Device], 'Table'[Month])
)

Create a calculated column

Category = 
IF(
[Avg Utilization] < 50, 
"Green", 
IF([Avg Utilization] > 50 && [Avg Utilization] < 80, 
"Amber", 
"Red"
)
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[Snapdrag]

Hello Kedar, Thanks for reply. I have already mentioned that calculated column can not work here. Assume a device falls in all three categoires then in which category device will fall in Pie chart. In pie chart we have option to paas legend and count of devices. I can't pass average utilization measure. 

[Kedar_Pande]

@Snapdrag 

Insted of calculated column, create a measure:

Device Category = 
VAR AvgUtilization = [Avg Utilization]
RETURN
IF(
AvgUtilization < 50, 
"Green", 
IF(AvgUtilization >= 50 && AvgUtilization < 80, 
"Amber", 
"Red"
)
)

Since the measure calculates the overall category based on the average utilization of the device, it will assign the correct category (Green, Amber, or Red) for each device in the Pie chart based on its overall utilization.