Forum Discussion
Creating table with combined text and measures?
Hello
I got some ideas about something similar on the below but my requirements have changed slightly and I would appreciate some specific input.
I want to perform several calculations on a Table1, e.g. Measure1 = AVERAGE(Table1[Column1]), Measure2 = SUM(Table1[Column2]) etc. Then, I want to put these calculations in another Table2 so that the result is:
TextA; TextB1; Measure1 TextA; TextB2; Measure2
The TextA1 etc are not contained in Table1, they are my own text/descriptions.
How can I create that table/matrix/visual?
I was thinking to first create a table as:
TextA1; TextA2; TextB1; TextB2;
I then created an index column in that table. I then tried to create another table with Calculated Columns as the Measure1 and Measure2 and to name these columns 1 and 2. I was hoping that I could transpose this table and then join it with the text table above based on the index numbers but it did not work.
Please note that I want to be able to create filter slicers so that I select TextA1 and it will display the TextA1; TextA2; Measure1 row only.
Can you briefly explain the strategy I need to follow along with the code so that I am able to interpret it.
Thanks!
PS: v-gizhi-msft posted this but I wonder if this is suitable https://community.powerbi.com/t5/Desktop/Is-a-way-to-achieve-a-view-of-a-table-matrix-with-measures-and/m-p/1132579/highlight/true#M515844
Can you give a little bit more info on what you need? Maybe provide:
1) sample data
2) how many measures/rows total will you need?
3) does the measure need to change/update based on filters?
If you don't have too many row or rather too many different types of rows in the desired table you can try using a similar strategy to this post:
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832
You could use some sort of summarize to create the measures, and UNION to stack them on top in rows, providing your own text is easy, for example you could simply write:
TABLE =
UNION(
SELECTCOLUMNS('Table1',"ColumnA","Text A1","ColumnB","Text B1,"ColumnMeasure",[Measure1]),
SELECTCOLUMNS('Table1',"ColumnA","Text A2","ColumnB",Text B2,"ColumnMeasure",[Measure2])
)However, that will be quite laborious depending on how rows you require, so if you can give us a bit more info on why and what you require, we can provide a better solution.
1 Reply
Can you give a little bit more info on what you need? Maybe provide:
1) sample data
2) how many measures/rows total will you need?
3) does the measure need to change/update based on filters?
If you don't have too many row or rather too many different types of rows in the desired table you can try using a similar strategy to this post:
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832
You could use some sort of summarize to create the measures, and UNION to stack them on top in rows, providing your own text is easy, for example you could simply write:
TABLE =
UNION(
SELECTCOLUMNS('Table1',"ColumnA","Text A1","ColumnB","Text B1,"ColumnMeasure",[Measure1]),
SELECTCOLUMNS('Table1',"ColumnA","Text A2","ColumnB",Text B2,"ColumnMeasure",[Measure2])
)However, that will be quite laborious depending on how rows you require, so if you can give us a bit more info on why and what you require, we can provide a better solution.