Percentajes in a matrix

[ancamar]

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?

[Bipin-Lala · Accepted solution]

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!

[ancamar]

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

Thank you very much!

Have a nice weekend!!!