How to add a row to calculate percentage in a matrix?

[Anonymous]

Hi, here is a question how to create a measure on row level in matrix.

for example, the last row is the result of a division, which 'competitive' is numerator and the row 'total' is denominator, do you have any good ideas?

Thank you.

[v-yangliu-msft]

Hi  @Anonymous ,

I created some data:

You can achieve this using create new table

Here are the steps you can follow：

1. Create calculated table.

Row_SubTotal =
var _summarize=SUMMARIZE(
    'Row_Table',[Date],
    "Name","Sum",
    "Value",SUM(Row_Table[Value]))
return
UNION('Row_Table',_summarize)

Summarize_Table =
var _summarize=SUMMARIZE(
    'Row_SubTotal',[Date],
    "Name","Total",
    "Value", 
   FORMAT( DIVIDE(SUM('Row_SubTotal'[Value])/2,SUM('Row_Table'[Value])),"Percent"))
return
UNION('Row_SubTotal',_summarize)

2. Result:

There are two things to note here:

1. You can replace "Competitve" with ''Sum', because Power BI sorts alphabetically by default. In my data, it is ABCDE. If it is written as "Sum", it will be in alphabetical order S>E&&S<T , placed after E and before Total.

2. Because the data format of a column must be the same, if one value is Whole number and the other is Percent, there will be a data type error, so I convert it to Text.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thank you. It's seems splendid, it's a good idea when data columns like that.

My biggest problem is the data is not in that form and the data should be shown originally, filtered by slicer, and add a row to calculate the percent based on certain other rows. So use a matrix to transpose is not meet the needs.

for example, show those data in a table and 4 slicers: region, org3.2, org4.2, org5, and add a row to calculate evary value column, the numerator  is the number when dim_2 is  'competitive' is and denominator is the number when dim_2 is 'firm' (total active%=160/217, fufilled%=65/35)

Is there to be a solution that add a row like that in this table, or add in new table with the result of calculation below the details table that remain the same slicers and same column name? Thank you.

[amitchandak]

@Anonymous , you can not add a row, You can change total using isinscope or hasonevalue to % total

https://xxlbi.com/blog/new-dax-function-isinscope/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Or you use this excel/power bi merge approach by Curbal

https://www.youtube.com/watch?v=IISYzTaIyu4