Forum Discussion
Dynamic matrix with categories/subcategories
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!
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:
Data Structure: Ensure your data includes categories, subcategories, transaction dates, and transaction amounts.
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.
Filter by Date:
- Apply a filter to the "Transaction Date" field to select the desired Year, Quarter, or Month.
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.
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:
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.
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.
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 = shownI 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.