Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 🙂
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
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.
@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 🙂
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
@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).
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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.
@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() ) )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |