Combine column totals to get total percentages

[Anonymous]

I have a table that is like this

               DIVISION 4    DIVISION 5   DIVISION 6 

                $200              $100               $400

                $150              $300               $200

TOTAL      $350              $400               $600

The total is summarized by Power BI on its own. I need a way to get the total of the three divisons added up, and then have the total of Division 4, 5, and 6 be divided by that total. For example, the total would be $1350, then I would need $350 / $1350 turned into a percentage. Then made into a PI chart with the 3 percentages at the end.

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Add an index column in Query Editor and unpivot other columns.

Then create a measure and create a martix visual as below.

Measure = SELECTEDVALUE('Table'[Value])/CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Index]))

Best Regards,

Jay

[parry2k]

@Anonymous do you have raw data in this pivoted format or it is a matrix view? If raw data is like this, you need to unpivot it for better design and then you can write measures to calculate % but first I would like to know how raw table looks like

[Anonymous]

I'm very new to Power BI and really do not know.

[parry2k]

@Anonymous the source from where you are pulling the data, division are on the columns or on the rows?

[Anonymous]

The columns. The total of the columns of each Division. The column totals need to all be added up and then divided back by the Division total. So if Divison 1 Total is 100 and Division 2 Total is 100, the Full Total would be 200 and the number I need would be 100/200, the percentage.

[Ashish_Mathur]

Hi,

Share thw raw data here in a format that can be pasted in an MS Excel file.