Group measures in a Matrix under a custom heading

[hitesh1607]

Hi - I have 3 tables

1) Measure Table under which all the calculations are done

Sales CY

Sales LY

Quantity CY

Quantity LY

2) fact table - qty, Sales, transaction date, CustomerId

3) Customer Table - CustomerId, Name

I need to create a Matrix-like this:

(Sales and Quantity will be the custom Names I want to group my measures by)

Customer	Sales	Quantity
	CY LY	CY LY
1	20 200	10 20
2	100 500	1000 2000

Thank you
Hitesh

[v-alq-msft · Accepted solution]

Hi, @hitesh1607 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Customer:

Fact:

Table:

You may create measures as below.

Sales CY = 
CALCULATE(
       SUM('Fact'[Sales]),
       FILTER(
           ALL('Fact'),
           'Fact'[CustomerId]=SELECTEDVALUE(Customer[CustomerId])&&
           YEAR('Fact'[Transaction date])=YEAR(TODAY())
       )
   )

Sales LY = 
CALCULATE(
       SUM('Fact'[Sales]),
       FILTER(
           ALL('Fact'),
           'Fact'[CustomerId]=SELECTEDVALUE(Customer[CustomerId])&&
           YEAR('Fact'[Transaction date])=YEAR(TODAY())-1
       )
   )

Quantity CY = 
CALCULATE(
       SUM('Fact'[Quantity]),
       FILTER(
           ALL('Fact'),
           'Fact'[CustomerId]=SELECTEDVALUE(Customer[CustomerId])&&
           YEAR('Fact'[Transaction date])=YEAR(TODAY())
       )
   )

Quantity LY = 
CALCULATE(
       SUM('Fact'[Quantity]),
       FILTER(
           ALL('Fact'),
           'Fact'[CustomerId]=SELECTEDVALUE(Customer[CustomerId])&&
           YEAR('Fact'[Transaction date])=YEAR(TODAY())-1
       )
   )

Result = 
var _category = SELECTEDVALUE('Table'[Category])
var _measure = SELECTEDVALUE('Table'[Measure])
return
SWITCH(
   TRUE(),
   _category="Sales"&&_measure="CY",
   [Sales CY],
   _category="Sales"&&_measure="LY",
   [Sales LY],
   _category="Quantity"&&_measure="CY",
   [Quantity CY],
   _category="Quantity"&&_measure="LY",
   [Quantity LY],
   BLANK()
)

Result:

Best Regards

Allan

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

[amitchandak]

@hitesh1607 , Not Directly Supported.

Refer this

https://community.powerbi.com/t5/Desktop/Matrix-Display-Values-above-Columns/td-p/256905