Forum Discussion
Put multiple measures into a table grid
Hi,
I have a whole series of measures that are all coming from a single table. I am trying to figure out how I can display them in a table where I input the row name and assign a metric as a value. I know I can put metrics into a table but that just gives me the values across columns.
So the numbers next to Conv, UPT, AOV etc. are all measures being calculated off of the table above. The Row labels are just text boxes. They are currently all being shown as Cards which is super cumersome to work with because I need to create 3-4 more sets and make sure each set is aligned/filtered individually.
I can't figure out any way to get them into a matrix or table via DAX.
The format of the table is:
Row_Lbl | Col_Lbl | Date | Value
1) Dmnd-Ops | TY | 2/4/2018 | 248.15
1) Dmnd-Ops | LY | 2/4/2017 | 150.50
7) Sessions | TY | 8/20/2018 | 14585
8) Orders | LY | 10/10/2017 | 84
Thanks
I was able to figure this out:
First I create a table in Edit Queries for each of my KPI's, one each for "TY" and "LY" (This Year/Last Year). The table just has a Column1 with the number 1 in it.
I place a Measure filtered for TY into the new "TY" table. I also add new Columns, one for the row lable ("Conv") and column lable ("TY"). Finall I add a column that is = to the measure I had just added because the DAX UNION function I will use later doesn't recognize Measures.
I did the same for the "LY" data.
Finally I use a UNION function to append the 2 tables into a 3rd new table so the column labels, row labels and the measure data that is sitting in a column line up. Function is "create new Table" and KPI_Combo = UNION(KPI_Conv_TY,KPI_Conv_LY)
After doing this I can generate the table at the bottom:
Now I can just do the same with other KPI's, i.e. create a table for each and UNION them into the KPI_Combo table.
5 Replies
Hey,
I'm not sure if I understand your requirement correctly but maybe you can the matrix visual a try and then use the option "Show on rows" from values band:
Hopefully this is what you are looking for.
Regards
Tom
- That doesn't really work in my case. The source table has 3 dimensions. Value, row label and column label. Column label is always "TY" or "LY". The row labels are "Sessions", "Orders", "Sales" etc.
The data points I'm trying to get into a table are all calculations off of that table like Conversion_TY= Orders/Sessions filtered for TY or AvgOrderValue_LY=Sales/Orders filtered for LY or CONV_%=Conversion_TY - Conversion_LY
So I end up with measures that I would like to show with a row label like "Conversion", one measure each under 3 columns, ie TY, LY, Variance.I was able to figure this out:
First I create a table in Edit Queries for each of my KPI's, one each for "TY" and "LY" (This Year/Last Year). The table just has a Column1 with the number 1 in it.
I place a Measure filtered for TY into the new "TY" table. I also add new Columns, one for the row lable ("Conv") and column lable ("TY"). Finall I add a column that is = to the measure I had just added because the DAX UNION function I will use later doesn't recognize Measures.
I did the same for the "LY" data.
Finally I use a UNION function to append the 2 tables into a 3rd new table so the column labels, row labels and the measure data that is sitting in a column line up. Function is "create new Table" and KPI_Combo = UNION(KPI_Conv_TY,KPI_Conv_LY)
After doing this I can generate the table at the bottom:
Now I can just do the same with other KPI's, i.e. create a table for each and UNION them into the KPI_Combo table.
So much this! Thank you TomMartens
