Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Put multiple measures into a table grid

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

sxt173

Advocate II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-08-2018
07:29 PM

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

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

Thanks

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

sxt173

Advocate II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-11-2018
10:47 AM

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.

4 REPLIES 4

Highlighted
##

**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

TomMartens

Super User IV

Re: Put multiple measures into a table grid

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-09-2018
06:33 AM

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

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

Highlighted
##

sxt173

Advocate II

Re: Put multiple measures into a table grid

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-10-2018
11:27 AM

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.

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

sxt173

Advocate II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-11-2018
10:47 AM

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.

Highlighted
##

abhishekk28

Regular Visitor

Re: Put multiple measures into a table grid

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-05-2019
05:47 AM

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.