cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculate percentage of total for matrix column

Hi,

 

I have data displayed in a matrix:

 

Percentage of column.PNG

 

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

 

Here is the  definition of the matrix:

Definición de matriz.PNG

 

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

@Anonymous

 

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



Lima - Peru

View solution in original post

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

 

View solution in original post

5 REPLIES 5
Vvelarde
Community Champion
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,

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

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

 

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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