cancel
Showing results for
Did you mean:
Helper I

## Rows Total

I have data with Rep name and its total sale in various rectors. Excel file with 4 rows as below:

 Rep Q1 Q2 Q3 Q4 A1 \$ 36,114,600 \$   65,771,922 \$ 20,900,400 \$ 16,500,000 A2 \$ 32,344,002 \$ 119,109,304 \$ 26,599,140 \$ 16,500,000 A3 \$ 32,480,600 \$ 173,724,356 \$ 31,960,000 \$ 31,000,000 A4 \$ 32,883,033 \$ 132,468,481 \$ 20,022,749 \$ 16,000,000

When I am trying to create a table visual, It is summing all the values instead of showing the same as above. And also I dont know why there is blank row at top. below is the output. When I do "dont summarise", its same as above but I want total row also also which goes away. I used Calculate(sum(Table[A1]) but still its the same. WHY is it summing everything and why is there an extra row at top? Please help

1 ACCEPTED SOLUTION
Super User

Hello @pam259. Here is what I would do:

Go to Power Query (via Home > Transform Data > Transform Data)

Then Right Click the "Rep" column, and choose "Unpivot Other Columns"

Then double click the "Attribute" column header and rename it "Quarter", then double click the "Value" column header and rename it "Sales".

Then at the top left, choose Close and Apply.

Now back out in the Report view, create a new measure:

``Total Sales = SUM( 'Table'[Sales] )``

Then create a Matrix visual, with Rep on Rows, Quarter on Columns, and Total Sales on Values.

In the Formatting pane, you can turn off Column subtotals under the Subtotals group.

The final result should look like this:

In case it's helpful, here's a link to PBIX file.

-Steve

Super User

Hello @pam259. Here is what I would do:

Go to Power Query (via Home > Transform Data > Transform Data)

Then Right Click the "Rep" column, and choose "Unpivot Other Columns"

Then double click the "Attribute" column header and rename it "Quarter", then double click the "Value" column header and rename it "Sales".

Then at the top left, choose Close and Apply.

Now back out in the Report view, create a new measure:

``Total Sales = SUM( 'Table'[Sales] )``

Then create a Matrix visual, with Rep on Rows, Quarter on Columns, and Total Sales on Values.

In the Formatting pane, you can turn off Column subtotals under the Subtotals group.

The final result should look like this:

In case it's helpful, here's a link to PBIX file.

-Steve

Announcements