Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sxt173
Advocate II
Advocate II

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.Measures2Table.JPG

 

 

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

😎 Orders | LY | 10/10/2017 | 84

 

Thanks

1 ACCEPTED SOLUTION

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)

FIELDS.JPG

After doing this I can generate the table at the bottom:OUTPUTS.JPG

 

 

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.

 

 

 

 

View solution in original post

5 REPLIES 5
TomMartens
Super User
Super User

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:

image.png

 

Hopefully this is what you are looking for.

 

Regards

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

So much this! Thank you @TomMartens 

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)

FIELDS.JPG

After doing this I can generate the table at the bottom:OUTPUTS.JPG

 

 

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.

 

 

 

 

Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.