Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors