Percentage of Total in Matrix

[Anonymous]

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:

[v-xiaotang]

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.

[bondonian]

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

[harirao]

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,

[lucent]

You can concatenate the total and percentage total together.

[Anonymous]

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 :

[v-xiaotang · Accepted 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.

[amitchandak]

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

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)

[amitchandak]

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

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

[Sol_Choque]

i have the same problem :'(