Turn off Total for non-additive measure in a matrix

Power BI
1

I have a matrix with Store data, e.g. Region, Store Name, Gross Sales etc. I am interested in the total for Gross Sales, however if I have a measure - let’s call it Sales Change On Year % - and it has relevance at a row level but is meaningless if summed, Is there a way to omit totals for this or other selected columns in a Power BI Matrix? Or is there a workaround?

2

Hi @Jamie,

A work around should no longer be required because in the Subtotals section
you can enable “per row Level” and/or “per column Level”

Once you’ve done that you can deselect the levels you don’t want displayed

image

3

@Jamie,

You can also control this from your measure in the following way:

Sales Change on Year % =

VAR SalesChng = blah blah blah

RETURN
IF( HASONEVALUE( SalesChng), SalesChng, BLANK() )
  • Brian
4

Many thanks @Melissa, that looks like exactly what I am after, however “Per Column Level” is not available as an option to me - I am on the latest version of PBI desktop… am I missing something?

5

Thanks @BrianJ This worked a treat, I just want to see if @melissas approach can be made to work!

6

Hi @Jamie,

For that option to show you need a dimension in the Columns section of the Matrix visual

image

7

Ah, I have nothing on columns, just the sum of the measures.

8

Hello @Jamie,

Thank You for posting your query onto the Forum.

Well I know that @BrianJ has already provided the solution and just thought of posting my solution in good spirits.

So is this type of results that you’re looking for? Below are the screenshots provided for the reference -

Part 1: Sub-Totals available at a row level and Grand Total. (And yes, they both are measures i.e. Total Sales and % Profit Margin)

Sub-Totals Available At A Row Level
Sub-Totals Available At A Row Level440×683 30.2 KB

Part 2: To hide the both sub-total and grand total of % Profit Margin. Below are the steps as suggested as follows alongwith the screenshot provided for the reference -

  1. Go to the “Format Pane”.

  2. Go to the “Field Formatting” section.

  3. Now, from the drop-down option select the "% Profit Margin" option.

Select % Profit Margin

  1. Change the font color to “White” or any color that matches with your report background.

  2. Disable and Enable the following options as per the screenshot below -

image

Part 3: Results showing where sub-totals and Grand totals are hidden

Resultant Output
Resultant Output432×690 29.6 KB

Hoping that this was what you were looking for and you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh