Forum Discussion

SRobbo1990's avatar
SRobbo1990
Frequent Visitor
4 years ago
Solved

Help with creating a table/matrix

Hi All,

 

I currently have 3 tables in a report

All of the fields in these tables are measures created. (I am using an AAS cube with live connnection with base metrics in).

The business has come back to me to ask if I could reformat these three tables to look like the following;

I've been going over and over about how to reformat this into some sort of matrix but haven't been able to come up with anything. 

Is there something obvious I'm missing.

 

Thanks

Sam

 

Need Help
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    4 years ago

    Hi SRobbo1990 

     

    What you can do is to create two disconnected table with the values you need:

    Table1

    • Enquiries
    • Quotes
    • Bookings

    Table2

    • Actual
    • Target
    • To Target
    • vTarget
    • vPY

     

    Now add this has your rows and columns to a matrix then create a measure similar to this one:

    Values for Matrix =Switch ( true(),
SELECTEDVALUE(Table1[Column])= "Enquiries" && SELECTEDVALUE(Table2[Column])= "Actual", [Enquiries],
SELECTEDVALUE(Table1[Column])= "Enquiries" && SELECTEDVALUE(Table2[Column])= "Target", [Target Enquiries],
SELECTEDVALUE(Table1[Column])= "Enquiries" && SELECTEDVALUE(Table2[Column])= "To Target", [To Taget Enquiries],
SELECTEDVALUE(Table1[Column])= "Enquiries" && SELECTEDVALUE(Table2[Column])= "vPy", [Enquiries vPY],...)

     

    This should have one line per each of your combinations and the corresponding metric for the match of both values. Then use this metric on the values of the matrix.

     

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi SRobbo1990 

     

    What you can do is to create two disconnected table with the values you need:

    Table1

    • Enquiries
    • Quotes
    • Bookings

    Table2

    • Actual
    • Target
    • To Target
    • vTarget
    • vPY

     

    Now add this has your rows and columns to a matrix then create a measure similar to this one:

    Values for Matrix =Switch ( true(),
SELECTEDVALUE(Table1[Column])= "Enquiries" && SELECTEDVALUE(Table2[Column])= "Actual", [Enquiries],
SELECTEDVALUE(Table1[Column])= "Enquiries" && SELECTEDVALUE(Table2[Column])= "Target", [Target Enquiries],
SELECTEDVALUE(Table1[Column])= "Enquiries" && SELECTEDVALUE(Table2[Column])= "To Target", [To Taget Enquiries],
SELECTEDVALUE(Table1[Column])= "Enquiries" && SELECTEDVALUE(Table2[Column])= "vPy", [Enquiries vPY],...)

     

    This should have one line per each of your combinations and the corresponding metric for the match of both values. Then use this metric on the values of the matrix.