topic Re: Grouping matrix columns using custom measures in DAX Commands and Tips

Grouping matrix columns using custom measures

DylanSandry — Wed, 19 Jun 2024 07:48:46 GMT

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.

Re: Grouping matrix columns using custom measures

Daniel29195 — Wed, 19 Jun 2024 09:26:10 GMT

DylanSandry

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 :

TotalPrice

and

TotalCount

for 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.

Re: Grouping matrix columns using custom measures

DylanSandry — Thu, 20 Jun 2024 02:00:18 GMT

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.