Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi guys,
Is there a possibility in a Matrix to show "% of Total per row and column" and only for Total, not for every column?
If I add a measure that calculates the percentage of rows I get this picture
As we can see the measure adds a column for every column and there will be a third column If I add the measure that calculates the percentage of columns, but I need only the percentage of Total, not for every value in the Matrix.
Something like this:
Solved! Go to Solution.
Hi @Anonymous
Thanks for your reply.
Based on your discription, you want to put row &column total& total percentage into matrix, right? However, the need has not been supported in Power BI yet. Because, in metrix the total is only one row/column. By the way, you can add an another metrix with row &column total.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Based on your description, I create a sample, see sample file attached bellow.
-
Create a measure:
Measure 2 = IF(ISINSCOPE('Table (2)'[orderdate])&&ISINSCOPE('Table (2)'[storecity]),SUM('Table (2)'[salesvolume]),"input value you want")
Result:
Then, replace the string “input value you want” with your measure [%_Of_Total_format]. Or use the measure bellow,
Measure =
VAR _percent =
DIVIDE (
SUM ( 'Table'[salesvolume] ),
CALCULATE ( SUM ( 'Table'[salesvolume] ), ALLSELECTED ( 'Table' ) )
)
RETURN
IF (
ISINSCOPE ( 'Table'[orderdate] ) && ISINSCOPE ( 'Table'[storecity] ),
SUM ( 'Table'[salesvolume] ),
FORMAT ( _percent, "0.00%" )
)
Result:
Hope it helps.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Thank you for this solution. It gets me closer to what i need to do, but my issue is with the sorting of the formatted string. this works, however my if statement returns dollar values $$$ or percentages, but they cannot be sorted because they are treated as strings. Can you think of a way to have the values treated like numeric values when sorting?
Thanks in advance,
mike
Hi @v-xiaotang @amitchandak,
I was going the attached pbix file, i am also looking for similar solution were i want to see row total & column subtotal's as well as count & percentage together below snapshot for your reference.
PBI which is refered (all blank should be 0)
Thank you
Regards,
Hi !
Thank you for your response! This is almost what I need, I have to show TOTAL VALUE and TOTAL %,
The measure above shows only Total %.
Perhaps you can help me out to show TOTAL VALUE and TOTAL %.
Something like this :
Hi @Anonymous
Thanks for your reply.
Based on your discription, you want to put row &column total& total percentage into matrix, right? However, the need has not been supported in Power BI yet. Because, in metrix the total is only one row/column. By the way, you can add an another metrix with row &column total.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , In the matrix, when right click on the measure in the visual pane, it should give the option for Percent or row total or percent of column total. Choose the required one and check
That option converts all values in percentages.
I need to show values, including total values and percentages only for Total values (both in Total Column and Total Row)
@Anonymous , with help from isinscope you can switch total
if(isinscope ([column column]) && not(isinscope([Row column]) ) ,[% of row total], [Measure])
% of row total = divide( [Meaure], calculate([measure], removefilters([Row columns]))
example
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Unfortunately, it doesn't help. New measure with "isinscope" works like that as I did before, shows percentages for all values, not only for Total
i have the same problem :'(
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |