Show % of total as a column total on Matrix visual

[Anonymous]

Hi There, I am in need of showing the grand % of total(%GT) on the Matrix visual. I tried to use the in-built functionality i.e Show value as-Percent of Grand Total. However, when I use this on the Values, the %GT shows up in each and every column value instead of showing on the column totals. Is there we can avoid showing this on each and every column ?

As you can see in the above screen, the %GT Count of Product shows next to each product. I only want to see towards the end of the row(the last one) as that is the grand total for that particualr Return Reason.

Appreciate your help.

Sample data:

ReturnReason	Product
Faulty	Door Mat
Late Delivery	Body Wash
Better Price	Door Mat
Changed Mind	Suitcase
Bad Condition	Sweatshirt
Other	Vaccum Cleaner
Late Delivery	Shampoo
Bad Condition	Shampoo
Better Price	Shoes
Other	Phone Case
Other	Keyboard
Faulty	Phone Charger
Late Delivery	Vaccum Cleaner
Changed Mind	Track Pant
Better Price	Desk
Bad Condition	Door Mat
Other	Bed Lamp
Late Delivery	Nail Polish
Better Price	Suitcase
Other	Track Pant

Thanks

Nama

[TheoC]

Hi @Anonymous 

I don't believe what you are wanting is achievable with the Matrix table currently.  What you can do is a workaround.  Basically, create two Matrix tables, hiding one behind the other. You will then get the output as per below:

I have attached a PBIX to assist.

Hope this helps!

Theo 🙂

[Anonymous]

Appreciate @TheoC for looking into this. Too bad we can't do this out of the box(visual, I mean) as it's quiet common to have this type of a reuirement. I will raise this as an idea on ideas site. Having said that, your hack might be a possible compromise but I am afraid their is some sort of maintanence with it. Thanks your help and time.

[TheoC]

@Anonymous unfortunately there will be maintenance in all things regarding this until the idea is accepted.  I believe this may have already been posted as an idea so hopefully this will be one of the future features should it get enough votes.

All the best either way!

Theo 🙂

[DataZoe]

@Anonymous I couldn't find a way to get the column to go away, but I did find a way to make it less visible:

for this I created a measure (Modeling ribbon > New measure) where I create the % of GT measure: 

% of Total =
IF (
ISINSCOPE ( 'Table'[Product] ),
BLANK (),
DIVIDE (
COUNTROWS ( 'Table' ),
CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )
)
)

The measure uses ISINSCOPE to make the % blank when the scope includes product specifically. I add this this to the matrix. Then I rename this measure in the matrix to a single space. This way it doesn't show in the matrix headers and values sections (it is still there though). 

[Anonymous]

Thanks @DataZoe I am not sure I would go with the proposed approach as I am not sure how it scale when we want to enhancements. Thanks for trying though.

[amitchandak]

@Anonymous ,

Try a measure = 

divide([measure], calculate([measure], removefilter(Table [Product]))

or

if(isfiltered(Table [Product])) ,

divide([measure], calculate([measure], removefilter(Table [Product]))

,divide([measure], calculate([measure], allselected() ) )

[Anonymous]

Thanks @amitchandak I am not sure whether I understand your advice correctly. By adding those measure on the matrix visual, they will still show up as values next each product, which is what I was trying to avoid. 

Thanks though for looking into this.