cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Percentage of Total in Matrix

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:

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous

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.

11 REPLIES 11
Community Support

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.

Frequent Visitor

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?

mike

Post Prodigy

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,

Regular Visitor
You can concatenate the total and percentage total together.
Anonymous
Not applicable

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 :

Community Support

Hi @Anonymous

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.

Super User

@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

Anonymous
Not applicable

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)

Super User

@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/

Anonymous
Not applicable

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

New Member

i have the same problem :'(

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors