Forum Discussion
Dynamic matrix with categories/subcategories
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
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:
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]))
Create similar measures for Quarter and Month by adjusting the filter condition accordingly.
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)
Adjust this measure for Quarter and Month as well.
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.
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.
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.Follow these steps:
Make sure you have the "ShowCategory1" measure and the matrix visual set up as previously described.
Select the matrix visual on your report.
Go to the "Format" tab in the right-hand pane to access the formatting options.
Expand the "Conditional formatting" section.
Click on "Background color."
Choose "Color scale" from the dropdown menu.
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.
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.