Displaying text and measures in table

[allymn789]

Is it possible to add text and a custom measure in individual cells and not apply to the whole column?  I am trying to build a table with 3 columns: Question asked on survey, average response, and aggregate data for all responses   (I have synced data on all pages so the average response will be calculated and is working on other pages on bar charts.)  Since I don't have a lot of questions I think it would be easiest to build a table by typing questions in column 1, have the system do a calculated measure in column 2 and type a single value for the aggregate data in column 3.

e.g. The Office is a funny show | 9 | 10

       Dogs are better pets than cats | 8 | 7

I don't know if it's possible to do this or put measures in individual cells.  Any thoughts on this would be welcome.  Thank you!

It may be too much information but I tried transposing data and loaded that spreadsheet so the survey questions can load into the table vertically rather than horizontally but now I have almost 1400 columns (responses from survey) so this isn't a feasible approach.

[v-alq-msft · Accepted solution]

Hi, @allymn789 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Table:

In Power Query, you may create a new query with the following m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYvBDQAgCAN34e1DjCLOQth/DUujjyPlChFi0uSABSZztuD2jQ6MTV2rPdTYl9b+z5g2cBaONFg6XX1mXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Question1 = _t, Question2 = _t, Question3 = _t, Question4 = _t, Question5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Question1", Int64.Type}, {"Question2", Int64.Type}, {"Question3", Int64.Type}, {"Question4", Int64.Type}, {"Question5", Int64.Type}})
in
    #"Changed Type"

Result:

Or you may create a table as below.

Table3:

Then you could create following two measaures.

Avg Question Measure = 
SWITCH(
    SELECTEDVALUE(Table3[Questions]),
    "Question1",
    AVERAGE('Table'[Question1]),
    "Question2",
    AVERAGE('Table'[Question2]),
    "Question3",
    AVERAGE('Table'[Question3]),
    "Question4",
    AVERAGE('Table'[Question4]),
    "Question5",
    AVERAGE('Table'[Question5])
)

Avg Aggregate Measure = 
var tab = 
ADDCOLUMNS(
    ALL(Table3),
    "Result",
    SWITCH(
        Table3[Questions],
        "Question1",
        AVERAGE('Table'[Question1]),
        "Question2",
        AVERAGE('Table'[Question2]),
        "Question3",
        AVERAGE('Table'[Question3]),
        "Question4",
        AVERAGE('Table'[Question4]),
        "Question5",
        AVERAGE('Table'[Question5])
    )
)
return
AVERAGEX(
    tab,
    [Result]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft · Accepted solution]

Hi, @allymn789 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Table:

In Power Query, you may create a new query with the following m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYvBDQAgCAN34e1DjCLOQth/DUujjyPlChFi0uSABSZztuD2jQ6MTV2rPdTYl9b+z5g2cBaONFg6XX1mXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Question1 = _t, Question2 = _t, Question3 = _t, Question4 = _t, Question5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Question1", Int64.Type}, {"Question2", Int64.Type}, {"Question3", Int64.Type}, {"Question4", Int64.Type}, {"Question5", Int64.Type}})
in
    #"Changed Type"

Result:

Or you may create a table as below.

Table3:

Then you could create following two measaures.

Avg Question Measure = 
SWITCH(
    SELECTEDVALUE(Table3[Questions]),
    "Question1",
    AVERAGE('Table'[Question1]),
    "Question2",
    AVERAGE('Table'[Question2]),
    "Question3",
    AVERAGE('Table'[Question3]),
    "Question4",
    AVERAGE('Table'[Question4]),
    "Question5",
    AVERAGE('Table'[Question5])
)

Avg Aggregate Measure = 
var tab = 
ADDCOLUMNS(
    ALL(Table3),
    "Result",
    SWITCH(
        Table3[Questions],
        "Question1",
        AVERAGE('Table'[Question1]),
        "Question2",
        AVERAGE('Table'[Question2]),
        "Question3",
        AVERAGE('Table'[Question3]),
        "Question4",
        AVERAGE('Table'[Question4]),
        "Question5",
        AVERAGE('Table'[Question5])
    )
)
return
AVERAGEX(
    tab,
    [Result]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[allymn789 · Accepted solution]

Thank you, v-alq-msft.  Sorry for the delay in responding.  You definitely recreated my data and problem!  Thank you for the detailed information and .pbix file.  I am new to M code (and Power BI) so it's taking me a while to work this into production but this is what I need.  Thanks again!

[allymn789]

Sorry that it is confusing.  Sometimes explaining these problems is harder than the solution. 

I am using the Table under Visualizations and dragging one question at a time into values.  I then have the system calculate average rather than the default sum.  This part is working.  However, the table displays horizontally rather than vertically.  Each question is a column header and the calculated average value is below it to the far right. I'd like to list the questions all in one column (A) and their averages in the column next to it (B).

Using the example above, I am getting this:

Average The Office is a funny show. Average Dogs are better pets than cats.

                                                      9                                                                8

When I need it to look like:

The Office is a funny show | 9

Dogs are better pets than cats | 8

I tried transposing the data on a different spreadsheet and importing it but now I'm stuck with 1400 columns (the number of respondants) and that doesn't seem right.  Or I don't know how to work with that.

I'll also need to add a third column where I show average aggregate data.  I have underlying, hidden filters on each page so users can filter on demographics such as age, gender, etc. The calculations are responding correctly to this.  I'd like to have that third column so there is a comparison of the subset of data to all respondants.  I'd be OK if I had to manually enter static data into that third row since there aren't that many questions.  I just don't know how to do it.

Thank you for your help with this.

[selimovd]

Hey @allymn789 ,

to be honest I don't really understand your question.

Why don't you put the question in the first column and then the 2 measures in the other?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi