Forum Discussion

reddevil's avatar
reddevil
Helper II
1 year ago
Solved

Table to be created using the below format

Hi Everyone: I have created a single measure for Prev and Current quarter and want to split it in a matrix using the below format, can someone guide me how do i achieve the below matrix table using ...
  • Akash_Varuna's avatar
    1 year ago

    Hi reddevil Could you try this please create measures first for three seperatly and then do SWITCH statement in dax please check this 

    1. Create Measures:
      Define prev , curr , and diff measures:

      PrevMeasure = CALCULATE(SUM(Table[Value]), FILTER(Table, Table[Quarter] = "Previous"))  
      CurrMeasure = CALCULATE(SUM(Table[Value]), FILTER(Table, Table[Quarter] = "Current"))  
      DiffMeasure = [CurrMeasure] - [PrevMeasure]  
    2. Dynamic Measure:

      SelectedMeasure = 
      SWITCH(
          TRUE(),
          SELECTEDVALUE(Table[MeasureType]) = "Prev", [PrevMeasure],
          SELECTEDVALUE(Table[MeasureType]) = "Curr", [CurrMeasure],
          SELECTEDVALUE(Table[MeasureType]) = "Diff", [DiffMeasure],
          BLANK()
      )
    3. Matrix Setup:

      Add Category and Measure to rows.

      Add MeasureType (Prev, Curr, Diff) to columns.

      Use SelectedMeasure as values.

    4. Apply conditional formatting for Direvtion as needed.