Forum Discussion
Put multiple measures into a table grid
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.
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.
Thanks sxt173 , this is the closest solution to the problem that I have seen all over the internet. I used this and it worked fine except for one small thing. My KPIs use time intelligence function, (the measures in original tables use Calendar[Date] in YTD and LM measures) and in absence of date field in the new tables, the measures do not give an exact value. Any idea/thoughts for solving this? Would be really helpful.
So much this! Thank you TomMartens