CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
coder_bg
coder_bg

Reputation: 370

How to get Matrix subtotal to the first column in Power BI

I have a matrix where different KPI's are coming from different tables Example:

      India   China   USA   Australia  Total
KPI1  2       3       4     5          14
KPI2  1       2       3     4          10
KPI3  8       10      10    8          36

The total column is COLUMN SUBTOTALS to ON in the MATRIX VISUAL and SHOW ON ROW is ON since the data in the base file had to be transposed in order to get this "look".

I want to get this column to the front but according to Power BI Community answers, it is currently not possible.

I was thinking of making a CALCULATED COLUMN or a MEASURE for this task but I don't know how to make it possible since these KPI are coming from different tables.

Any help on this is very much appreciated or some other workaround?

EDIT

SAMPLE DATA

Teams | Country | KPI 1
    A | India   | 3

in the same way, all KPIs are stored in different sheets of the excel file.

OUTPUT

     Total India   China    USA   Australia  
KPI1  14     2       3       4     5          
KPI2  10     1       2       3     4          
KPI3  36     8       10      10    8

Upvotes: 0

Views: 6948

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

There isn't a simple way to move the total column to the left, at least in the current version of Power BI (as of when this answer was written).

There is a workaround but it's a bit of a pain. Instead of using automatic subtotals, you create "Total" rows in your data tables as if it were any other country.

Once Total exists as a country, you add an index column to define the display order using the Sort by Column button so that Total appears first.

Both appending the total data rows and adding the country ordering index should probably be done in the query editor.

Upvotes: 1

Related Questions