Forum Discussion
Post ProdigyTable Sort - Can't be more than one value...
Hello - I am needing help sorting a matrix visual, where the displayed row has common ReportLine value, but account drill down provides the greater detail.
First screenshot is the underlying table. ReportLine is a calculated column, with unichar(to allow for sorting) by column Sort.
1st - the calculated column utilizes this measure -
VarHeader =Rept(UNICHAR(8204),CountSp)&SELECTEDVALUE('IncomeBalance x Ledger'[Report Line initial])&Rept(UNICHAR(8204),DecimalCount)
With this calculated column, the table sorting works great.
However, but including this unichar, the matrix visual believes the row values are separate, and segregrates. I would like to maintain a single 'Service Revenue", and then pull in the ledger description. In this second screenshot "Service Revenue" is displayed multiple times.
The problem I am having - I need the matrix sorted by column Report Line, at the same time, matrix visual displays as a single value. Everything is working with exception of the matrix splitting out based on the calc column.
Is there a different unichar that might be used?
The desired result is a single 'Service Revenue" line, that can be sorted back at the table level.
Appreciate any help that can be provided! Thank you!
5 Replies
- amitchandak
Super User
Dellis81Thank you for your thoughts. Even though I am using "selectedvalue" within the measure, the result of the calculated column/measure appears to be correct - and allows for proper sorting. Possibly this second screenshot shows the problem I now have with matrix visual. The items grouped in blue should be condensed to a single row. If I remove the calculated column, then the sorting option results in an error. Thank you!
Hi Dellis81 ,
According to the screenshot, OPERATIONAL COSTS field seems to have a hierarchy which may be part of the reason why it is obviously the same field but not automatically combined.
It's a little hard for me to follow, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Dellis81
Thanks for your response. I understand the difficulty of understanding without the underlying file. Attached is a simplified sample file that might help in our communication. https://1drv.ms/u/s!AmBVCme14p7xqESUBs3wQudF7RY4?e=YUIfZ4
Yes, there are hierarchies. I am attempting to build an income statement utilizing the report template within our accounting software. For demo purposes, I am only including end result tables. (I imported from a live working data model)
I think this is the table we need to focus on. IncomeBalance x Ledger.
1) index to a primary table with Report headers (for example "Operational Costs")
2) Report lines to display within Report Headers ie "Seed Corn Grower Costs" or "Supplies:Diesel"
3) Within each report line, could be 1-100 accounts included. For example Supplies:Diesel has 9 differing ledgers.
4) The sort column is hte sorting order I would like to see in the report.
You will notice in the demo, for both Header and Detail tables, I have a caculcated column "adding spaces" or using hte Unichar(8203) or 8204. I had read, these two Unichars return a zero width space?
Thank you again.
- Dellis81
Hello Eyelin - thank you for your followup again. Several days ago, I posted this response to your initial request.
PS - I am having difficulty with the PBI forum converting to Spanish vs English. I do have in my preferences to us the default US English. So, I apologize if I'm not completely getting what you need. I appreciate your help, I need to get this resolved. thanks
Thanks for your response. I understand the difficulty of understanding without the underlying file. Attached is a simplified sample file that might help in our communication. https://1drv.ms/u/s!AmBVCme14p7xqESUBs3wQudF7RY4?e=YUIfZ4
Yes, there are hierarchies. I am attempting to build an income statement utilizing the report template within our accounting software. For demo purposes, I am only including end result tables. (I imported from a live working data model)
I think this is the table we need to focus on. IncomeBalance x Ledger.
1) index to a primary table with Report headers (for example "Operational Costs")
2) Report lines to display within Report Headers ie "Seed Corn Grower Costs" or "Supplies:Diesel"
3) Within each report line, could be 1-100 accounts included. For example Supplies:Diesel has 9 differing ledgers.
4) The sort column is hte sorting order I would like to see in the report.
You will notice in the demo, for both Header and Detail tables, I have a caculcated column "adding spaces" or using hte Unichar(8203) or 8204. I had read, these two Unichars return a zero width space?
