Figure 6 Using a side table to display cross filtered detail.Ģ. Figure 5 While the same ‘first drill down row’ captures detail necessary at the expense level.Īs an alternative, we can skip the need for the user to drill down entirely by by providing a side table to the right that cross filters the main financial statement when a specific line is selected. Figure 4 – Our first drill down row now groups Sales properly by Customer name only. In Figure 5 we see our first level Expense lines now includes the other required detail expense items such as Doc# and Vendor# and a Note field. With the Sales result in Figure 4, we see only one level drilled down, and Sales is properly grouped only by Customer name. Finally, our variable “MATH” will bring it together into the result.įigure 3 DAX calculated column to capture only necessary fields in each record. By doing so, we will call the SALES variable in Sales, and EXPENSE variable in Expense lines. Next, we identify when our DAX is in a Sales vs. DOC# is our document number VEN# is our vendor number etc.). We’ll first use variable to ignore blank data fields, then assign a helpful data label that leads and distinguish say a document number from a vendor number for clarity (i.e. We will call this new column Line2 to represent the second row level in our matrix. Now let’s use some DAX (Figure 3) in a calculated column (Or for better performance, use a custom column in Power Query) to help us define that ideal single line of information that will help us pick and choose what we display on that first drill down row. Figure 1 – Stacks of table fields as row levels Figure 2 – Fully expanded original matrix. What we get instead is every statement field, including blank fields that happen to be relevant to show for expense lines, such as document number, vendor name, and an explanation field. All we really need here is just to show sales grouped by the customer name. In just a few clicks, we see how a nice-looking summary visual can quickly stretch out and turn into excess rows, taking up valuable space in the report.įigure 2 below illustrates this when looking at our Gross Sales line. This format would fully expand to the visual you see in Figure 2. In our early matrix visuals, we’d stack all the informational table fields in the row fields of the matrix (Figure 1). As a result, we only need one drill down row level to find what we need, no more, and no less. This solution uses DAX in a calculated column to pick out and combine relevant detail specific to the statement line type. They needed easy access, and were not interested in earning a black belt in the drill-down feature. We tried to show them the different drill in options to clean the report view, to no avail. Users would see a hierarchy of sometimes blank rows taking up valuable reporting space and rarely find what they were looking for. We found that by asking a reader to drill into multiple row levels regardless of the statement line type, it only took about three clicks for the report to lose its familiar shape. Roll up relevant detail into one single row level: However, ask your report users to navigate this visual and you may quickly discover that many users are unsure how to fully take advantage of the details under the surface of the initial visual itself.īy applying some simple and creative ideas, we were able to provide a user friendly matrix that brought information to the surface quickly, and helped tell a clear data story.ġ. The ability to summarize, search, and drill into transaction detail makes it a fast friend of any Accountant or Financial Analyst. It’s no surprise that a program aimed at the pivot table loving business user would include a similar visual called the Matrix.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |