Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Solution Specialist
Solution Specialist

Hello @Anonymous. 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
Solution Specialist
Solution Specialist

Hello @Anonymous. 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.