cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

## Calculate percentage of total for matrix column

Hi,

I have data displayed in a matrix:

(I removed sensitive first column, but there is a first column with product names)

Here is the  definition of the matrix:

What I want to do is that every column percentage sums 100% at the bottom, My tries always gives as the percentage of the whole data.

My variable definition is as follows:

% Bat Presencia = var paso1= CALCULATE(SUM('BATERÍAS'[Veces]),ALLSELECTED()) return sum('BATERÍAS'[Veces])/paso1

This works nice when there is one column.

Help is greatly appreciated.

2 ACCEPTED SOLUTIONS
Community Champion

@Anonymous

```% Bat Presencia =
VAR paso1 =
CALCULATE (
SUM ( 'BATERÍAS'[Veces] ),
ALLEXCEPT ( 'BATERíAS', 'BATERíAS'[Medicion] )
)
RETURN
SUM ( 'BATERÍAS'[Veces] ) / paso1```

Lima - Peru
Employee

@Anonymous

You can't remove other filters in table1, but keep a column for context filter from table2 reserved. Even you have built the relationship between two tables, it will return entire table1 as context without taking the values from [MEDICION] column. You have to put the [MEDICION] into table1 to group your calculation as you have done.

Regards,

5 REPLIES 5
Community Champion

@Anonymous

Hi, if i understand correctly

Do You need every column Sum 100% and each row the Correct %. Example: (12483/19003)=65.7%

Lima - Peru
Anonymous
Not applicable

That is correct, I need that the percentages shown total 100% en every column.

Regards,

Community Champion

@Anonymous

```% Bat Presencia =
VAR paso1 =
CALCULATE (
SUM ( 'BATERÍAS'[Veces] ),
ALLEXCEPT ( 'BATERíAS', 'BATERíAS'[Medicion] )
)
RETURN
SUM ( 'BATERÍAS'[Veces] ) / paso1```

Lima - Peru
Anonymous
Not applicable

Fixed!!!

With your proposed solution a got 100% on every row of the table, my measure "MEDICION" comes from another table. I moved MEDICION to the analysis table and now it is fixed. I do not have the time to figure out why a valid related column from another table was causing the problem.

Thank you!!!

Employee

@Anonymous

You can't remove other filters in table1, but keep a column for context filter from table2 reserved. Even you have built the relationship between two tables, it will return entire table1 as context without taking the values from [MEDICION] column. You have to put the [MEDICION] into table1 to group your calculation as you have done.

Regards,

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors