Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ancamar
New Member

Percentajes in a matrix

Hello everyone.

I'm new to using Power BI and I'm hitting a wall with a problem that I can't seem to solve.

I have a dataset with the columns 'limits' (contracted limit) and 'current consumption' with more info like type of consumption and so on.  

Company

Type

Limit

Consumed

A

Advance

100

80

A

Simple

20

0

A

SMS

20

0

B

Advance

100

90

B

Simple

100

50

 

My idea is to complete the data with the percentage consumed using this DAX formula: Cons_porc = data[current_cons]/data[limit]

 

Company

Type

Limit

Consumed

Cons_porc

A

Advance

100

80

0.8

A

Simple

20

0

0

A

SMS

20

0

0

B

Advance

100

90

0.9

B

Simple

100

50

0.5

 


The problem arises when I attempt to build a matrix like this:

ancamar_0-1708619456603.png

 

All individual types of consumption are being calculated correctly, but the top one (general) is simply summing up the individual values and not performing the calculation of 191 divided by 2206.

My configuration is (but i tried to change but doesnt work)

ancamar_1-1708619621818.png

 

Any ideas?

1 ACCEPTED SOLUTION
Bipin-Lala
Resolver IV
Resolver IV

Hi @ancamar ,

 

It seems like you have created a calculated column for calculating the percentages. Since calculated columns work on row context (current row), hence you are getting correct percentages for each row, but incorrect values at sub-total levels. It is simply summing up the percentage values in each row.

 

You can solve this problem by creating a calculated measure instead.

For example - the following shows the sample dataset I created (based on your data)

BipinLala_0-1708621072933.png

The below matrix visual compares the output of consumption percentage calculated via calculated column and measure. You will get the results you were looking for in the field Consumption Percentage Measure.

BipinLala_2-1708621214868.png

The measure created is as follows - 

Consumption Percentage Measure = CALCULATE(SUM('Table'[Consumed])/SUM('Table'[Limit]))

CALCULATE function helps with filter context. For each individual row, we are getting the correct percentage. At the level of Company sub-total, the values are summed up and then the percentage is calculated, as per the formula.

 

You can understand the difference between the outputs of using calculated columns and measures by seeing them side-by-side as shown in the matrix above.

I hope it's the output you were looking for!

View solution in original post

2 REPLIES 2
Bipin-Lala
Resolver IV
Resolver IV

Hi @ancamar ,

 

It seems like you have created a calculated column for calculating the percentages. Since calculated columns work on row context (current row), hence you are getting correct percentages for each row, but incorrect values at sub-total levels. It is simply summing up the percentage values in each row.

 

You can solve this problem by creating a calculated measure instead.

For example - the following shows the sample dataset I created (based on your data)

BipinLala_0-1708621072933.png

The below matrix visual compares the output of consumption percentage calculated via calculated column and measure. You will get the results you were looking for in the field Consumption Percentage Measure.

BipinLala_2-1708621214868.png

The measure created is as follows - 

Consumption Percentage Measure = CALCULATE(SUM('Table'[Consumed])/SUM('Table'[Limit]))

CALCULATE function helps with filter context. For each individual row, we are getting the correct percentage. At the level of Company sub-total, the values are summed up and then the percentage is calculated, as per the formula.

 

You can understand the difference between the outputs of using calculated columns and measures by seeing them side-by-side as shown in the matrix above.

I hope it's the output you were looking for!

Wow, that worked flawlessly! It was as simple as that. You saved me!

Thank you very much!

Have a nice weekend!!!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.