calculating percentage at all levels in Power Bi in Matrix

[Anonymous]

Hi,

I'm trying to create a column that calculates percentage from top level till the least level in a matrix.

Here is the sample of my data set. the column here is the calculated column for percentage.

                                                                                                                                                                                                                                                                                          

I'm creating a matrix like this. Column 1, 2, 3 in rows and Value1, 2 and calculated column in values. I have selected to average the percentage calculated column.

At the least level the calculation of percentage is correct. For Example, In the calculation for F3 for 01 May 2018 (10/12)*100 = 83.33. This is correct.

But at F3 level (33/144) * 100 = 22.91 is the correct one. But here it averages the 3 values beneath it. I know that is because I have chosen to average it. But I want this percentage to be calculated at all levels correctly. Also the total should be calculating the same thing.

Kindly assist.

[TomMartens · Accepted solution]

Hey,

maybe you should use a measure instead of a calculated column like this:

SUM('Table1'[Value2]) / SUM('Table1[Value1]) * 100

I think this is what you are looking for. The measure creates the sums first and then performs the division.

Wondering if this is what you are looking for.

Regards,

Tom

[Anonymous]

Perfect ! Is there a way to show this as %. If it is a column I can do it from formatting in Modelling tab. Is there a way to do the same for a measure ?