Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
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?

daniyarurazov_0-1621395960085.png

If I add a measure that calculates the percentage of rows I get this picture

daniyarurazov_1-1621396149176.png

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:

daniyarurazov_2-1621396831335.png

 

1 ACCEPTED SOLUTION

Hi @Anonymous 

Thanks for your reply.

v-xiaotang_0-1622015680577.png

 

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.

View solution in original post

11 REPLIES 11
v-xiaotang
Community Support
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:

v-xiaotang_0-1621593006351.png

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:

v-xiaotang_1-1621593128448.png

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.

Excel.PNG
PBI which is refered (all blank should be 0)
PBI%matrix.PNG

 

Thank you

 

Regards,

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 :

daniyarurazov_0-1621851057141.png

 

Hi @Anonymous 

Thanks for your reply.

v-xiaotang_0-1622015680577.png

 

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.

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

 

Percent of Total Matrix.png

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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)

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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

i have the same problem :'(

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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