I am frustrated by the lack of customization available in a table viz if I transpose the rows and columns. Is there any way to get the same formatting options I do for columns if I want my measure to appear down the left rather than across the top?
Transposing Tables
1 Like
Hi Kelly. Can you talk more about which customization options don’t seem to be available in this case? The options themselves are the same when the metric set is transposed, the difference is that now there are different measures in each row but the usual formatting options are still located within the Columns properties, meaning those customizations might now be applied to different measures within a single column.
Hi Jamie,
I’m not sure where to find the options for formatting that you’re describing. I’m working in version 10, and the properties that I see are very limited. Transposing, my options in the properties menu go from:
to
My current pain point is I can no longer apply custom text to any of the cell values. The client wants the Avg Speed to Answer and Avg Handle Time presented as HH:mm:ss, which I was able to fake before transposing by creating formulas that break out the hours, minutes, and seconds into separate measures and then reassembling them as a custom text in the row properties. I tried fudging it in the transposed version by putting custom text in a state, but the formulas aren’t available in my text options.
What would you recommend?
I can see your issue, and it is partly because the formatting options haven’t changed even though the data has - they are still organized by columns even though you want to refer to and separately customize different measures, which are now in rows. Sometimes states can help with this since they are still separated by measure, but would you consider a different solution to the problem, such as a formula (in advanced mode) like this that uses a TimeSpan:
var span = new TimeSpan(0, 0, $Seconds$);
return (Math.Floor(span.TotalHours) * 10000) + (span.Minutes * 100) + span.Seconds;
(The TotalHours property would return a higher number than 24 when necessary if you aren’t reading the Days property, etc., then calling Math.Floor cuts out any fractions of hours because those are being read separately by the Minutes and Seconds properties.)
This should produce numbers containing all the digits you want for HH:mm:ss, and then it looks like it can be formatted with a custom format such as 00:00:00 in the measure’s own settings rather than using the table’s properties.
@jamie.cherwonka, I have another example of the issue we have with customization on a transposed table.
I want to the Start of Week and End of Week to replace the Week number and year on the header, but I can’t do custom text in the same way I can when it’s not transposed. Is there a work around for this issue as there is for the time?
Unless I’ve missed something about your case, those options are still the same: the column’s Header Text property can still be set to any combination of text & keywords for the data producing columns (under Rows in the Data Analysis Panel when the metric set is transposed). What I guess you are running into is that this column header is not displayed by default if you only have a single column in the Properties window for the table, because this is normally just blank without any purpose for that single column repeated under every week & program above. If so, would you consider removing the other rows of column headers from under “Row Header” (because the metric set is transposed) in the Data Analysis Panel’s Visualization tab? Once the other rows of column headers are removed, the column’s set Header Text from the Properties window will be all that’s left to display and will be visible.
If you had any use for a second column such as state indicators displayed to the right of your numbers, you could instead click + to add another one in the Properties window for the table and that will also cause the text set up in the columns’ Header Text property to display. (I guess it would also be possible to make this second column appear blank or set it to a width of zero. Until now it’s been assumed that a single column’s header text is not displayed unless the other rows of headers are removed.)
@jamie.cherwonka, you are correct I am able to get the custom text to show by removing all the row headers but one. Thank you for that clarification. For this case though, I do need two header rows for the week and the program and I’d prefer to have the label for the week be the values from start of week and end of week. Is there any scripting or other work around magic I can perform or is this something that can be looked at for future versions?
This is something that can be looked at in a future version, but for now the set header text will also display when there is more than one column. This is true even if the 2nd column is hidden by unchecking its Visible property, resized to a width of zero or otherwise not distinctly visible. Dashboard viewers can show and hide columns by default from the right-click/context menu, but the Change Data privilege determines if this is available. (For now please refresh the data in this particular case when showing or hiding a column if it does not appear correct at first as an issue has been identified here.)
Hi Kelly - I’ve just re-read part of your post:
If you want to replace the week number and year with the start and end dates, this is something that a time dimension’s formatting options are designed to provide for you. You can edit the time dimension, expand Formatting, select the Week level, then set the Caption Format like {CWStart:MMM dd, yyyy} - {CWEnd:MMM dd, yyyy} where the portion after CWStart/CWEnd is customizable. (You can also create a separate time dimension for this and then edit it as described above if you want to use different formatting for weeks in different places.) These formatting options are described more here including a section about week start/end formatting.
I misread your post earlier and thought you wanted both the week numbers and the dates at the same time in separate rows, which could have been more difficult to accomplish using just time dimension formatting although I think that would also be possible. I also added a feature request for the ability display custom header text for a single column when the metric set is transposed.