cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BINAMA
Regular Visitor

Show % of total as a column total on Matrix visual

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.

BINAMA_0-1651794005500.png

Appreciate your help.

Sample data:

ReturnReasonProduct
FaultyDoor Mat
Late DeliveryBody Wash
Better PriceDoor Mat
Changed MindSuitcase
Bad ConditionSweatshirt
OtherVaccum Cleaner
Late DeliveryShampoo
Bad ConditionShampoo
Better PriceShoes
OtherPhone Case
OtherKeyboard
FaultyPhone Charger
Late DeliveryVaccum Cleaner
Changed MindTrack Pant
Better PriceDesk
Bad ConditionDoor Mat
OtherBed Lamp
Late DeliveryNail Polish
Better PriceSuitcase
OtherTrack Pant

 

Thanks

Nama

7 REPLIES 7
TheoC
Super User
Super User

Hi @BINAMA 

 

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:

 

TheoC_0-1651799973499.png

 

I have attached a PBIX to assist.

 

Hope this helps!

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

BINAMA
Regular Visitor

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.

@BINAMA 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 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

DataZoe
Microsoft
Microsoft

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

 

DataZoe_1-1651799531379.png

 

 

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

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

BINAMA
Regular Visitor

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
Super User
Super User

@BINAMA ,

 

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() ) )



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

Power BI Dev Camp Session 25 with aka link 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Top Solution Authors
Top Kudoed Authors