Forum Discussion

reddevil's avatar
reddevil
Icon for Helper II rankHelper 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 ...
Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    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.

V-yubandi-msft's avatar
V-yubandi-msft
Icon for Community Support rankCommunity Support
1 year ago

Hi reddevil ,

In additionto the Akash_Varuna , response.  To enhance the accuracy and flexibility of your time based filtering, replace string filters with proper time intelligence functions like QUARTER(TODAY()) or DATEADD().  This ensures calculations dynamically adjust to the current date context instead of relying on static text-based filters.

 

Additionally, use VAR to store reusable values and maintain a dynamic date context that aligns with reporting periods. This improves clarity, performance, and consistency across different time-based metrics.

 

If your table contains multiple metric types, consider creating separate dynamic measures (Selected_OpenIssues, Selected_ResolutionTime) to accurately represent distinct reporting needs. This method provides greater flexibility in visualizing and analyzing various data categories within your report.

 

i Hope this helps...