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
WL
Regular Visitor

Subtotal not available?

Hi all, 

Appologies in advance if this questions have been asked many many times, and I'm very new to this realm  

 

I'm running a set of data via power query and has dumped them into a Pivot Table via Power Pivot

 

So far so good however the result in Pivot Table can't do Subtotal? 

 

In the Filed Setting the Custom option is greyed out 

 

Can someone please point out the reason and possible solutions? 

 

Regards,

Wendy

 

 

1 ACCEPTED SOLUTION

I have added a link and updated my previous screen shot. See if that has solved your problem. On Layout & Print Option under Field Settings you need to enable additional items to enable subtotals

View solution in original post

9 REPLIES 9
shivakumarsokke
Regular Visitor

Not sure if I have understood the problem here 100%. But here is what I find that works for me. 

 

I believe you have established the conenctions  under Data tab from where you are reading the source data in the Excel and have selected the Pivot table option under the Insert. 

 

More details on using Pivot with Subtotals Here

 

 

 

 Pivot.png

 

I think you are 100% correct @shivakumarsokke

 

What's reason for that? and how to avoid it in the future? 

 

I'm sort of practice and learn in the same time 

 

Wendy

I have added a link and updated my previous screen shot. See if that has solved your problem. On Layout & Print Option under Field Settings you need to enable additional items to enable subtotals

Thanks @shivakumarsokke I didn't realised this is a pure pivot table issue when bringing data from external source 

 

The Layout & Print setup has solved my problem, however it's more of a work around? as you can't really do other functions like count etc.

 

Is there are permanent solution under Excel? or have to use Power BI desktop

 

Thanks again 

v-juanli-msft
Community Support
Community Support

Hi @WL

Based on my knowledge, it is possible to do a pivot table in Power BI.

However, your requirement is not clear, could you show an screenshot or examples?

 

Please see How to Get Your Question Answered Quickly

 

Best Regards

Maggie

Hi Maggie @v-juanli-msft

 

Please see below screenshot highlighed with the problem is. 

I'm in excel 365

The background data is loded in via power query, I'm trying to subtotal the Amount by Program Type. The Amount is not calculated and I have formated as Currency 

 

I can try Power BI to see if the poblem will dissapear but at the end of the day I still need Excel to run this particular file 

 

Capture 1.PNG

 

 

Thanks

Wendy

Hi,

 

Share the link from where i can download your Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

Thanks for offering the help, 

 

I'm so sorry is there an instruction or somthing on how to create a downlad excel file? 

 

Thanks

 

WL
Regular Visitor

Has anyone experienced such issue when doing a quick pivot table?

 

 

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.