Hi,
I have an interesting scenario. I need to sort line_item by a column named line_sort. However, this sorting changes for each line item according to the reportview column. For example, If the report view is Income Source, then the line_sort for closing balance line item is 16. However if the report view is income statement, then the line_sort for closing balance line item is 8.
When we visualize this data, we apply a filter to the visual based on the report view column (income statement, income source etc) so that each visual will have unique line items and line_sort. The line_sort should not be shown in the visual.
I am using Zebra BI and normal metrix and I could not find a way to sort the visual by a column that is not present in the visual.
an example of dummy data is below.
The visuals will only show Amount by line items (sorted by line_sort but not shown in the visual). Each visual will be filtered by the reportview
One way for this to achieve was to use the feature where we click on the column line_item in the data property and in the "order by" feature, and selecting the "line_sort" column. Hower, since for the same line_item "closing balance", we have multiple line_sort (per report view, 8 and 16), this feature is not working for us and is throwing an error.
Can someone please guide me how to achieve this sorting for the line items?
Hi @arifshah
Based on your question, here's what I assume you might want to achieve:
Here's some dummy data
"Line item"
"Line sort"
If you want to sort line_item based on line_sort, try the following. Create a column,
line item sort =
LOOKUPVALUE(
'Line sort'[line_sort],
'Line sort'[reportview],
'Line item'[reportview ]
)
Select Zebra BI Tables, here is the result.
If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you for the reply and your solution. The scenario is a bit different for me. In my case, the line items are repeating and therefore, one line item may have multiple line_sorts. For example, take a look at the attached dummy data. Please note that at a time, the visual will be filtered by the reportview so that will not show multiple reportviews in the same visual. Can you please explain how would your solution work in this case?
The visuals will only show Amount by line items (sorted by line_sort but not shown in the visual). Each visual will be filtered by the reportview
