cancel
Showing results 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.

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:

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)

Any ideas?

1 ACCEPTED SOLUTION
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)

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.

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!

2 REPLIES 2
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)

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.

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!

New Member

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

Thank you very much!

Have a nice weekend!!!

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors