Forum Discussion
Two row headers without stepped layout
Sorry for the title of this thread. I couldn't think of a better title to summarize my problem.
I have a table with two columns CompanyNumber and CompanyName among others. Values in column CompanyNumber are distinct such that there aren't more than one row with the same CompanyNumber. So a CompanyNumber has a unique CompanyName.
Now I want to have a matrix in Power BI with row headers CompanyNumber and CompanyName, but I don't want them to be stepped, because I know there is always just one row for every CompanyNumber and I just want to display also the CompanyName, side by side.
I know, that I can turn off the stepped layout to achieve that they are side by side, but then Power BI still assumes that there CAN be more than one row for one CompanyNumber. And then for every row there is a total row, even though it is unnecessary.
If I turn off the row sub totals then the total of all rows is away, too. But I want the total of all rows to be shown.
And I don't want to use a usual table but a matrix, because I want to take advantage of the columns field.
I know, I could create a column that is a concatenation of CompanyNumber and CompanyName and only add this column as a row header, but then it is not possible to sort by either CompanyNumber or CompanyName.
I hope I have been able to explain my problem clearly. Is it possible to achieve what I want?
You can control row subtotals for each field. That allows you to keep the row total but suppress all subtotals, for example.
Thanks, I now switched on the "Apply settings to: Per row level"
I don't know, why I haven't figured that out earlier, because before I wrote this thread, I have clicked around everywhere in the settings. 🤔
And I don't understand why the option "Per row level: on" causes it to let the subtotals disappear, but the main thing is that it works.
4 Replies
You can control row subtotals for each field. That allows you to keep the row total but suppress all subtotals, for example.
Thanks, I now switched on the "Apply settings to: Per row level"
I don't know, why I haven't figured that out earlier, because before I wrote this thread, I have clicked around everywhere in the settings. 🤔
And I don't understand why the option "Per row level: on" causes it to let the subtotals disappear, but the main thing is that it works.
Yet another problem:
I cannot sort by the second row header (CompanyName). It is only sorted in the context of the first row header (CompanyNumber). Since I always have exactly one CompanyName for a CompanyNumber it makes no sense. Again: I don't want to use a normal table, because I want the "Pivot Table like" feature with dynamic columns and so on.
Is my use case so special? I don't understand why it is so complicated.Use "sort one column by another column" and sort company number by company name