cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sbrick
Frequent Visitor

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? 

 

Any help would be appreciated.

2 REPLIES 2
tejaswidmello
Impactful Individual
Impactful Individual

Hi sbrick,

 

You can simply turn off the Total Setting from the Format ..

 

Capture 11.PNG

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. 🙂

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!