Dynamic matrix with categories/subcategories

[AlexXandyr]

Hello,
Im trying to create a Matrix that shows two layers, category1 and subcategory2. Where I only want to see Category1 if subcategory2 have more entries than 1 for the specific year chosen in a filter.

Right now I will see the below where highlighted in red I want to get rid of. So only if there are more than 1 in Subcategory2 with amounts for the choosen year I want it to be displayed. 

[Anonymous]

Hi @AlexXandyr 

You can refer to the following solution.

Sample data 

Sample slicer data

The relaionship

Create a measure

Measure =
VAR a =
    COUNTROWS (
        FILTER ( ALLSELECTED ( 'Table' ), [Category] IN VALUES ( 'Table'[Category] ) )
    )
RETURN
    IF (
        ISFILTERED ( Slicer[Year] ),
        CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', a > 1 ) ),
        CALCULATE ( SUM ( 'Table'[Amount] ) )
    )

Then put the field to the matrix visual

Output

Best Regards!

Yolo Zhu

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

[123abc]

In Power BI, you can achieve the desired functionality of displaying Category 1 only if Subcategory 2 has more than 1 entry for the specific year chosen in a filter by using measures, slicers, and conditional formatting. Here's a step-by-step guide on how to do this:

1. Data Model: Ensure that your data model contains the necessary columns, including the year, Category 1, and Subcategory 2.

2. Create Measures:

   • Create a measure that calculates the count of entries in Subcategory 2 for the selected year. You can use the COUNTROWS function for this. For example:

Subcategory2 Count = COUNTROWS(FILTER(YourTable, YourTable[Year] = SELECTEDVALUE(YearFilter)))

Create another measure to determine if Category 1 should be displayed or not based on the count of Subcategory 2. This measure could be a simple IF statement. For example:

Display Category1 = IF([Subcategory2 Count] > 1, [Category1], BLANK())

1. Slicer for Year:

   • Create a slicer that allows the user to choose the year. This slicer should be connected to the relevant table or column containing the year information.

2. Matrix Visualization:

   • Create a matrix visualization in your report.
   • Place the "Year" field in the Rows area.
   • Place the "Display Category1" measure in the Values area.

3. Conditional Formatting:

   • To hide the Category 1 values when the count of Subcategory 2 is less than or equal to 1, you can apply conditional formatting to the "Display Category1" measure. Here's how to do it:
     • Click on the measure in the Values area of the matrix.
     • Go to the Format pane.
     • Under "Conditional formatting," select "Color scale."
     • Set the color for values less than or equal to 1 to be the same as the background color (e.g., white) to effectively hide them.

Now, when a user selects a specific year in the slicer, the matrix will display Category 1 only if Subcategory 2 has more than one entry for that year. If Subcategory 2 has one or no entries, the Category 1 value will be hidden due to the conditional formatting.

This approach allows you to dynamically control the visibility of Category 1 based on the count of entries in Subcategory 2 for the selected year.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[AlexXandyr]

Dear @123abc ,
Thank you very much for the very detailed reply. This helps me definitely along the way. I realize now that I should have added some more information to my problem. 

Behind the year data in the pictures I can have multiple transaction (by date) aggregating up to the amounts seen in the pictures. So transactions can be multiple but only if Subcategory2 are more than 1 for the Category1 it should show in the matrix.  Also the filter used in the matrix needs to be able to be dynamic for Year, quarter and month. Hope it clarify the complexity a bit more.

Tried to explain it below

Thank you!

[123abc]

I see that your requirement involves dynamic filtering for Year, Quarter, and Month, and you want to display Category1 in the matrix only when Subcategory2 has more than one transaction within the selected time frame. To achieve this, you may need to use a more advanced approach using Excel or similar spreadsheet software. Here's an extended guide to address these additional complexities:

1. Data Structure: Ensure your data includes categories, subcategories, transaction dates, and transaction amounts.

2. Create a Pivot Table:

   • In Excel, select your data range, go to the "Insert" tab, and click "PivotTable." In Google Sheets, go to "Data" and select "Pivot table."
   • In the PivotTable Field List, drag "Category1" and "Subcategory2" to the "Rows" area.
   • Drag "Transaction Date" to the "Filters" area.

3. Filter by Date:

   • Apply a filter to the "Transaction Date" field to select the desired Year, Quarter, or Month.

4. Subcategory2 Count:

   • Add the "Subcategory2" field again to the "Values" area of the PivotTable.
   • Change the aggregation function for "Subcategory2" to "Count" to count the number of transactions.

5. Filter by Subcategory2 Count:

   • Apply a filter to the "Count of Subcategory2" to only display Category1 when the count is greater than 1 for the selected Year, Quarter, or Month.

To make the filter dynamic for Year, Quarter, and Month:

1. Create Helper Cells for Date Selection:

   • In a separate area of your spreadsheet, create cells where you can select the Year, Quarter, and Month. These can be dropdown lists or input cells.

2. Use Helper Cells for Filters:

   • Change your "Transaction Date" filter in the PivotTable to refer to the values in the Year, Quarter, and Month selection cells. This allows you to dynamically filter data by changing the values in these cells.

With this setup, you can select the Year, Quarter, or Month in your helper cells, and the PivotTable will automatically update based on your selection. The Category1 will only be displayed if Subcategory2 has more than one transaction within the selected time frame.

Keep in mind that the exact steps may vary depending on your spreadsheet software, but this approach allows for dynamic filtering and the display of Category1 based on your specified criteria.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[AlexXandyr]

Thank you again @123abc 
Its actually not transaction that should be the determinator if it should be shown or not in the matrix. But instead if there are more than one Subcategory2 in the Category1 for the chosen time period. As shown in my previous example where Charlie and Delta should not be shown even though they have 39 respectively 15 transactions. But Category1 should show even though they only have one transaction per subcategory. The important part is the Subcategory2 column within the selected period. Less than one Subcategory2 for each specific Category1 = not to be shown, more than 2 Subcategory2 for each Category1 = shown

[123abc]

I understand your requirement better now. You want to show Category1 in the matrix if, within the selected time period (Year, Quarter, or Month), there are more than one unique Subcategory2 entries for each specific Category1. Here's how you can achieve this:

1. Create Measures for Subcategory2 Count by Category1 and Time Period:

   • Create a measure for Year:

Subcategory2CountYear = COUNTROWS(SUMMARIZE(FILTER(YourTable, YourTable[Year] = [SelectedYear]), YourTable[Category1], YourTable[Subcategory2]))

1. • Create similar measures for Quarter and Month by adjusting the filter condition accordingly.

2. Create a Measure to Determine Whether to Show Category1: Now, you need a measure that checks if there are more than one unique Subcategory2 entries for each specific Category1 within the selected time period:

ShowCategory1 = IF([Subcategory2CountYear] > 1, 1, 0)

1. Adjust this measure for Quarter and Month as well.

2. Use Slicers or Filters for Year, Quarter, and Month: As before, set up slicers or filter visuals for Year, Quarter, and Month to allow users to select the desired time period dynamically.

3. Use the ShowCategory1 Measure in the Matrix: In your matrix visual, use the ShowCategory1 measure as a filter or as part of your matrix to determine whether to show Category1. You can use this measure in combination with the Category1 field.

Now, your matrix will only display Category1 when, within the selected time period, there are more than one unique Subcategory2 entries for each specific Category1. If the condition is met, it will show Category1; otherwise, it won't.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[AlexXandyr]

Thank you @123abc 

I created these measures and adding them to the matrix shows the below which is correct. Now I want to filter "ShowCategory1" so it only shows where Subtotal says 1. But if I filter now then nothing shows up since the filter looks at each invidual row for ShowCategory1 and not on the orange subtotals. 

So if a orange row for subtotal says 1 it should be visible, if it says 0 it should not show in the matrix. 

[123abc]

Follow these steps:

1. Make sure you have the "ShowCategory1" measure and the matrix visual set up as previously described.

2. Select the matrix visual on your report.

3. Go to the "Format" tab in the right-hand pane to access the formatting options.

4. Expand the "Conditional formatting" section.

5. Click on "Background color."

6. Choose "Color scale" from the dropdown menu.

7. In the "Minimum color" and "Maximum color" sections, select the same color (e.g., white or the background color of your report) to effectively hide the Category1 rows where Subtotal equals 0. This will make them blend into the background.

8. Adjust the color scale settings as needed for your specific design preferences.

Now, the Category1 rows where the Subtotal value equals 1 will have a visible background, while the Category1 rows where the Subtotal value equals 0 will effectively be hidden as they will blend into the background.

This approach allows you to visually filter out Category1 rows with Subtotal values of 0 while showing those with Subtotal values of 1.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.