Forum Discussion

hitesh1607's avatar
hitesh1607
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Group measures in a Matrix under a custom heading

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)

 

CustomerSalesQuantity
 
CYLY
CYLY
1
20200
1020
2
100500
10002000

 

Thank you
Hitesh

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

2 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.