I have a dataset for employee training stored in long format, with each course listed in a "Course Title" Column and the Status in another column. I want the data to show in the dashboard in wide format, with each Course Title being its own column and the Course Status showing as the values. This was fairly easy to accomplish, but what I need is a total column that is able to count across the rows and sum up the number of completed courses per user. Creating calculated fields works fine on its own but does not show properly with this wide view.
I've done a rough version of what I want, but it required putting numbers into the values pane of the dashboard, which I do not want. If there is another way to go about this, or at least a way to hide the numbers within the cells while keeping the totals visible, that would help. Thanks.
If I follow correctly, you want the text in the body of the table and a number in the total column? You can cheat the table by returning two different values, depending on which part you're looking at:
IF SIZE() = 1 THEN
STR(SUM([Completed Courses]))
ELSE
[Course User Status]
END
The SIZE function is a table calculation that tells you how many items there are in the Dimension(s) you select, kind of like a COUNT DISTINCT (but more efficient). The size of totals is always 1, so as long as your main table contains more than 1 column (or whichever Dimension(s) you choose) you can switch between what is displayed. You only caveat is that the output has to be the same type, so I've had to cast the grand total as a String.
