Forum Discussion
Sorting Issues for a Bar Chart
Regarding the first sort, is a field from a DIM table (like an item master, customer master) on the axis of the bar chart? the sort by options wont' show up if you are using a field from the FACT table (like a customer key in a sales table). In that example, use the customer number/name from the Customer master table.
On the second sort, in a table I assume, get rid of the fake column where you muitiplied by -1. Sort by the comparison column. then sort again. It toggles from A..Z to Z..A each time you sort.
Here is a decent article on it.
In summary, a FACT table are your facts - the sales data for example. A sales table might have a customer number, item number, invoice number, invoice date, quantity, and amount.
A DIM table are your dimensions, or sometimes called master tables. So a customer master/DIM table would have your customer number, customer name, address, zip code, phone number, etc.
In Power BI and Excel with Power Pivot, you want those in separate tables. Before Power Pivot, you had to put all of that in one big flat table so Excel's Pivot Table function worked.
With Power BI and Power Pivot, you want those in separate tables, then you join them. This page has a pretty good overview of joining and how you'd use a Star Schema pattern (FACT table in the middle, DIM tables around it) when joining. Power BI works best with a Star Schema.
Regarding the first sort, is a field from a DIM table (like an item master, customer master) on the axis of the bar chart? the sort by options wont' show up if you are using a field from the FACT table (like a customer key in a sales table). In that example, use the customer number/name from the Customer master table.
On the second sort, in a table I assume, get rid of the fake column where you muitiplied by -1. Sort by the comparison column. then sort again. It toggles from A..Z to Z..A each time you sort.
Thanks for the quick response. I'll show my ignorance (and if you have the time I would appreciate enlightenment) by admitting I don't really know what a DIM table is vs. a FACT table. I'm pulling these data from an Excel spreadsheet.
I did have account code on the bar chart axis, and, upon closer scrutiny, I realized the axis took those as ordinal numbers and made the axis as a sequence of numbers incremented by one, your typical numerical axis. I changed the data type of the account code from whole number to text and whalah, the axis looked right and the sorting worked. It even showed up in the elipses drop down menu. I will take you advice and jettison the calculated column and use the actual column instead. I appreciate your help.
Again anything you can teach me about DIM and TABLE would be most appreciated.
Here is a decent article on it.
In summary, a FACT table are your facts - the sales data for example. A sales table might have a customer number, item number, invoice number, invoice date, quantity, and amount.
A DIM table are your dimensions, or sometimes called master tables. So a customer master/DIM table would have your customer number, customer name, address, zip code, phone number, etc.
In Power BI and Excel with Power Pivot, you want those in separate tables. Before Power Pivot, you had to put all of that in one big flat table so Excel's Pivot Table function worked.
With Power BI and Power Pivot, you want those in separate tables, then you join them. This page has a pretty good overview of joining and how you'd use a Star Schema pattern (FACT table in the middle, DIM tables around it) when joining. Power BI works best with a Star Schema.
Quick followup: The UI for sorting has changed quite a bit in the July 2018 desktop app. There are now separate buttons to sort ascending/descending, and to sort by specific columns. See the video this month. Sorting UI changes are discussed starting around 35:40.
