Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need help with Matrix report

I have created a matrix report as shown below: Now I need to add below 2 columns to the above report for the month of April(Latest Month) only.  However if I add above 2 fields to the '...
  • Ritaf1983's avatar
    2 years ago

    Hi Anonymous 
    Unfortunately, we don't have the option to add measures as another column in the matrix.
    But there is a workaround with dynamic measure that you can apply to get the desired result.

    These are the steps that you can apply :
    1. Create a table with the 2 measures that you need to add to the end of the matrix :

    2. Append it to the table with the categories that you have on the matrix's columns :

     

    Note: the columns' names should be the same :

    3. at the new table (result of append), remove all columns except for categories :

    4. remove duplicates:

    5. Switch off the enable load of the "measures table", you don't need it :

    6. add an index column, you will need it to sort the columns of the matrix:

     

    7. close and apply, and note that the table should be disconnected (with no relationship):

    7. create 3 measures :

    1 for the "columns" that you have now  and 2 for the new measures 

    8. create a dynamic measure for a matrix using our new table as a condition :

    Dynamic_measure = SWITCH(SELECTEDVALUE('Measures for matrix'[Category]),
    "measure 1",[measure_1],
    "measure 2",[measure_2],
    [sum])

    9. sort the column of categories + measures by index :

    10. create the matrix :

    The pbix with the example is attached

     

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