cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Advocate II
Advocate II

Re: 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)

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

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Put multiple measures into a table grid

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
Highlighted
Advocate II
Advocate II

Re: Put multiple measures into a table grid

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.
Highlighted
Advocate II
Advocate II

Re: 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)

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

Highlighted
Regular Visitor

Re: Put multiple measures into a table grid

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
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors