'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create a Power BI Donut Chart with Conditional Categories
Question details
The user needs to categorize Power BI dataset rows into ZTA FOC, IOC, or Neither for a donut chart without triggering a single-value determination error.
- Product
- Power BI
- Device & OS
- not provided
- Scenario
- Creating a donut chart visualization based on multiple column conditions (Capability IOC and Capability ZTA FOC).
- Observed behavior
- Using measures for row-level logic causes a single-value determination error. A row-by-row categorization is required to populate the chart legend.
Ensure you have access to edit the data model in Power BI Desktop and that your dataset contains the 'Capability IOC' and 'Capability ZTA FOC' columns.
Use a DAX Calculated Column with SWITCH Logic
Create a calculated column instead of a measure to evaluate each row individually, avoiding aggregation errors.
When you need to categorize individual rows based on multiple conditions, a calculated column is the appropriate choice. Unlike measures, which aggregate data on the fly, a calculated column evaluates your DAX logic row by row during data load, making it perfect for chart legends.
Open Power BI Desktop and navigate to the Data view or Report view from the left sidebar.
Select your target table (e.g., 'List of Capabilities') in the Fields pane, right-click, and choose 'New column'.
Enter the following formula: Category = SWITCH( TRUE(), 'List of Capabilities'[Capability ZTA FOC] = "YES", "ZTA FOC", 'List of Capabilities'[Capability IOC] = "YES", "IOC", "Neither" ) and press Enter.
Switch to the Report view, click the Donut Chart icon in the Visualizations pane, and add it to your canvas.
Drag the newly created 'Category' column into the Legend field of the donut chart. Finally, drag a capability identifier column (or any unique ID) into the Values field to count the categories.
Analyze Data and Create Stunning Charts with WPS Spreadsheet
While Power BI is a powerful tool for complex DAX logic, everyday data visualization and categorization can be effortlessly handled in WPS Spreadsheet. It offers a lightweight, highly compatible alternative to Microsoft Excel for generating donut charts, using intuitive IF/IFS formulas without the steep learning curve of DAX.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the IOC and ZTA FOC data.
- 2. Add a formula column: Use the =IFS() function to categorize your rows (e.g., checking for 'YES' values) directly in the grid.
- 3. Insert a Donut Chart: Highlight the categorized data, navigate to the Insert tab on the ribbon, and select the Donut Chart option to visualize your results.
Frequently Asked Questions
Why do I get a single-value determination error in Power BI DAX?
This error occurs when a DAX expression expects a single scalar value but receives a table or multiple values. Using a calculated column instead of a measure for row-level categorization resolves this issue.
Can I use an IF statement instead of SWITCH in Power BI?
Yes, you can use nested IF statements in DAX. However, using SWITCH(TRUE(), ...) is much easier to read and maintain when you have multiple independent conditions, such as checking for both ZTA FOC and IOC statuses.
What is the difference between a measure and a calculated column in Power BI?
A calculated column evaluates data row by row during the data refresh, making it ideal for static categories or dimensions (like chart legends). A measure aggregates data on the fly based on user interactions and filters, making it suitable for calculations like totals and averages.
How do I properly count the categories in my Power BI donut chart?
Once your conditional category column is added to the chart's Legend, drag a unique identifier column (like a Row ID or Capability ID) into the Values field. Ensure the aggregation method for that field is set to 'Count' or 'Count (Distinct)' in the field drop-down.
