Here are the general steps you can follow to achieve this in Power BI:
1. Data Preparation: Ensure that your data model has a column that can be used to identify the different segments within your 'Sub-sector' column. If such a column does not exist, you may need to create it.
2. Creating Subtotals: You can create subtotals by using DAX measures in Power BI. For example, you can create a measure that sums the values for a particular segment:
Subtotal Measure = CALCULATE(SUM('Table'[ColumnName]), 'Table'[Sub-Sector] = "Specific Segment")
Replace 'Table' with your actual table name and 'ColumnName' with the field you want to sum.
3. Matrix Visual: Use the Matrix visual to display your data. In this visual, you can drag your 'Sub-sector' into the Rows area and your measures into the Values area.
4. Row Grouping: In the Matrix visual, you can turn on the 'Subtotals' option for row groups. You can manage the visibility of the subtotals in the formatting options of the Matrix.
5. Conditional Formatting: If you need the subtotals to stand out, you can apply conditional formatting to the Matrix visual to differentiate the subtotal rows from other rows.
6. DAX for Conditional Subtotals: If the subtotal needs to be conditional (i.e., only for certain segments), you may need a more complex DAX measure that uses logic to determine when to calculate a subtotal vs when to just show the sum for the category.
7. Custom Visuals: If the built-in functionality of Power BI doesn't meet your needs, you might want to explore custom visuals that are available in the Power BI visuals marketplace that could offer more sophisticated grouping and subtotal features.
Here is an example of a conditional subtotal measure:
Subtotal Measure = IF( HASONEVALUE('Table'[Sub-Sector]), SUM('Table'[ColumnName]), CALCULATE( SUM('Table'[ColumnName]), FILTER( ALL('Table'[Sub-Sector]), 'Table'[Sub-Sector] = "Specific Segment" ) ) )
This measure checks if there is only one value in the 'Sub-Sector' (meaning it's a detail row), it will just sum the column. If there are multiple values (meaning it's a subtotal row), it will calculate the sum for a specific segment.
Remember, the specific DAX formulas and approach may vary depending on the structure of your data and the specific requirements of your report.
