Remove total value for one column in PowerBI

Question

I have a table visualisation in PowerBI that sums the top 10 products sold by sales quantity. I have a calculated column which shows the rate of sale, using other fields from the data source:

(quantity / # stores with product) / weeks on sale

The ROS calculates correctly, but it still sums and appears in the total row.. The number of stores and number of weeks are set to 'Don't Summarize', but they still add together and give some meaningless number in the total row. If i set ROS to 'Don't Summarize', to remove the total row, the summing of the rest of the table and therefore the filter I have on top N by quantity drops out.

It is very frustrating! Is there an option somewhere to simply not display total for a field?? I don't want to remove the total row completely as the other fields (e.g. Qty, Value, Margin) are useful to see a sum of.. It seems very strange that it is so difficult to do something so minor..

Additional info:

Qty is a SUM field.

Stores is not summarized and simply refers to the average number of stores that stock that product over the weeks of the trading season Weeks is not summarized.

Weeks is not summarized and refers to the weeks that have passed in the trading season.

Example data:

Item.......Qty......Stores.....Weeks....ROS

---

Itm1........600........390.........2............0.77

Itm2........444........461.........2............0.48

Itm3........348........440.........2............0.40

Total.....1,392.....1,291*......6*...........1.65*

Fields marked with a * are those where the sum is a meaningless figure unrelated to the data. I do not actually need Stores and Weeks to show in the table, so the fact that they sum does not matter. However, ROS is essential, but the sum part is totally irrelevant and I do not want it to show. Any ideas? I am open to the idea of using R to overcome the lack of flexibility in the standard tables although my knowledge in this area is fairly limited.

Answer 1

You can remove individual totals for columns in tables and matrix objects in a round-about way by using field formatting.

Click the object, go to formatting, click the field formatting accordion, select the column or columns you want to affect from the drop-down list, set the font color to white, set 'apply to values' to off, and set 'apply to totals' to on.

A bit tedious if you have many columns, but you will have, in affect, whited-out the column totals.

Heads up, you might still have a problem with exporting data, though.

Cheers

Answer 2

I suspect you've made a common mistake - using a Calculated Column for ROS where you should've used a Measure.

If you rebuild that calculation as a Measure, then you can wrap the HASONEVALUE function around it, with the objective of showing a blank when there are multiple Item values in context (the Total row).

Roughly the Measure formula would be:

ROS = IF ( HASONEVALUE ( Mytable[Item] ) , << calculation >> , BLANK() )

I would also replace your use of / with the DIVIDE function, to avoid divide by zero errors.

Answer 3

select do not summarise option for those metrics which you dont want total

Answer 4

Click on the table -> Fields -> expand the value field you don't want to include -> Select "Don't Summarize." This will exclude it from the "Total" row.

Answer 5

Select the table you want to change In the Visualizations pane: Go to Format, Find the Field Formatting option, Choose the field you don't want to summarize. Turn off 'apply to header', Turn off 'apply to values', Turn ON 'apply to total', Change the font color to white.