Removing subtotals from specific columns in tables while still summarizing that field in the table
We have an issue where our Orders and (Site) Visits are double counted across categories because it's very common for our customers to visit our website and view/order products from multiple categories or multiple products within a category (eg. Fruit & Vegatables, or Apples & Oranges).
However, those would still only count as one order or visit overall, therefore we do not want to summarize these for an entire table since that gives an inaccurate number for total orders and total visits when compared against other reports which do track distinct orders and distinct site visits; but we DO want them summed up for each particular row (at the category, brand, product levels which are shown on different tabs).
When I check the "Do not summarize" option for these fields, these tables then break out into multiple rows for each category, brand, etc.
Is there a way to summarize the row at that level but hide the total from the subtotal bar in a table and matrix?? Is there a special DAX formula or button to click that we're just missing or is that not an option?
I'm afriad it's not that simple. We DO want the table to have the subtotals but we do NOT want them for those two specific columns. Here is an example:
2 people visit our website, one person looks at just apples and orders an apple for $5 the other person lookst at apples and oranges and then purchases each for $10. At the product level within a category that would be two views for apples and one view for oranges as well as two orders with apples and one order with oranges, which are all correct at the row level.
However, that would then add up in the subtotal to three total visits and three total orders which is not correct since there were only two distinct visitors to the site and two distinct orders.
We do want the category level to show total revenue of $15 because that is correct. But we do NOT want the column to show 3 total visits or 3 total orders because that is not correct and it is misleading/confusing to those viewing the report.
For the record, our website does not sell fruit, it's just an example. 🙂