Forum Discussion
Grouping matrix columns using custom measures
Hello,
I've been destroying myself trying to figure this out and I wonder if someone can help.
I have the following table (Simplified to get to the root of my problem):
| Job | Product | Price | SaleDate |
| 1 | A | $100 | 01062024 |
| 1 | B | $200 | 01062024 |
| 2 | A | $110 | 19062024 |
| 3 | C | $300 | 18062024 |
| 4 | A | $120 | 01052024 |
I would like to display it as follows:
| Today | ThisMonth | ThisYear | ||||
| Product | TotalPrice | TotalCount | TotalPrice | TotalCount | TotalPrice | TotalCount |
| A | $110 | 1 | $210 | 2 | $330 | 3 |
| B | 0 | 0 | $200 | 1 | $200 | 1 |
| C | 0 | 0 | $300 | 1 | $200 | 1 |
I can flatten the table and created individual measures for every combination of column (e.g. TodayTotalPrice and TodayTotalCount), but I don't want a flat table. I want the nicely structured matrix that can be collapsed and expanded.
If anyone could please help for this simple example that would be great. I'm confident I'll be able to expand it to my larger dataset.
Thanks.
2 Replies
- Daniel29195
Community Champion
do the following :
create a custom table : ( No need for the Column "Column")add column1 to the matrix.and now you just need to create 2 measures :
andTotalPrice TotalCountfor each measure, use the switch statement :
switch(true ( ),selectedvalue(table[column1]) = "Today" , calculate( [total price] , date = today () ,selectedvalue(table[column1]) = "This Month" , calculate( [total price] , year(sales[order date]) = year ( today() , month(sales[order date]) = month( today()) ,.........., ,let me know if this helps. That's worked perfectly thanks!
Just a follow up question. Is it possible to show and hide certail values based on matrix columns? I want to also add averages after the TotalPrice and TotalCount sections, so it will look like this
Today ThisMonth ThisYear MonthAverage YearAverage Product TotalPrice TotalCount TotalPrice TotalCount TotalPrice TotalCount PricePerDay CountPerDay PricePerDay CountPerDay I can create the second section of matrix columns with a couple new measures and altered switch statements, but how can I hide the values that dont correspond to that section of the table? (Hide TotalPrice, TotalCount from the MonthAverage and YearAverage sections and vice versa).
Cheers.
