cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pam259
Helper I
Helper I

Rows Total

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

RepQ1Q2Q3Q4
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

 

pam259_0-1641585157116.png

 

1 ACCEPTED SOLUTION
SteveHailey
Super User
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"

SteveHailey_7-1641587665802.png

 

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

SteveHailey_0-1641587973379.png

 

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.

SteveHailey_6-1641587643780.png

 

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

SteveHailey_1-1641588032475.png

 

The final result should look like this:

SteveHailey_4-1641587610623.png

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

-Steve

 

View solution in original post

1 REPLY 1
SteveHailey
Super User
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"

SteveHailey_7-1641587665802.png

 

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

SteveHailey_0-1641587973379.png

 

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.

SteveHailey_6-1641587643780.png

 

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

SteveHailey_1-1641588032475.png

 

The final result should look like this:

SteveHailey_4-1641587610623.png

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

-Steve

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors