I found some information that you can use HASONEFILTER() to determine if there is a total being calculated instead of an individual row. I took a few days away, and came back and found a way around this. However, if all the values in a subsection were the same, or there was only one entry, it would still give me that value in the subtotal. Knowing that it would have a list of values from different records, I found I could use the following to display nothing for most totals: PASSENGER NAME = SELECTEDVALUE('Travel Data',BLANK()) I was able to figure out that if you used a DAX measure, it could be aggregated differently than the basic aggregations, since it could evaluate arbitrary DAX code. The first thing I determined is that if I just put a raw text field into the Values section of a matrix, it would ALWAYS try to aggregate it in some way for the subtotal. Then it would do the same for the subtotal, but this time it would get a list of all names in the Unit Section and Unit Name, selecting the 'first' one and calling that a total. There was only one, so it displayed correctly. My data is grouped by Unit Section -> Unit Name -> Record Key, so for each normal entry it would filter and find the first Name that matches the Record Key. It selects all the data, but filters at one level above that section's row groupings.įor my example, I'll assume it's calculating the Passenger Name field. It doesn't just take all the values above and sum/average/find them. It took some digging, but I was able to figure out that the way Matrices calculate the totals/subtotals of a column is weird. So I've managed to figure out a solution to my problem, and I'm sharing in case anybody else wants to only show specific totals or subtotals in a matrix. If it's helpful, here is a redacted version of what I want the report to resemble (doesn't have to be exact):ĮDIT: Crossed out questions I figured out the answers to. I only want the dollar total and advance totalled if possible. Is there a way to total only applicable values? Showing the first alphabetical Locator and earliest departure date is useless info. Is there some way to not show the Record Key value while still separating records by it? It's not human readable and just adds noise to the dataĤ. If not, is there a way to set up multiple columns worth of data, putting each of these records next to each other in their appropriate groupings?ģ. Is there a way to display each record horizontally instead of vertically?Ģ. Here is what I am currently able to display, as well as my field options:ġ. I've gotten around some of my issues by using a unique index as a 3rd grouping variable, which allows me to display each trip once I drill down. I've gotten pretty close, but I'm continually running into the problem that a matrix wants/needs to summarize every value. I'm also trying to subtotal each Unit's amount spent and advance before moving on to the next unit. I've tried using a table, but I can't group the records together. I wish to group this data first by Department (Unit Section in example), then by Unit (Unit Name in example), and then order within the group by locator. I'm trying to visualize travel expense data in a tabular format.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |