cancel
Showing results for
Did you mean:
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
Microsoft

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

Microsoft

@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

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors