Home

Problem with Pivot tables

%3CLINGO-SUB%20id%3D%22lingo-sub-481116%22%20slang%3D%22en-US%22%3EProblem%20with%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481116%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20facing%20a%20problem%20with%20data%20arrangement%20in%20a%20pivot%20table.%20When%20configuring%20my%20data%20base%20table%2C%20the%20lines%20are%20composed%20of%203%20products%2C%20while%20the%20columns%20contain%20information%20about%20number%2C%20cost%20etc.%20One%20column%20contains%20the%20month%2C%20and%20I%20have%20to%20repeat%20each%20month%203%20times%20down%20to%20include%20the%203%20products%20each%20month.%20Some%20other%20columns%20involve%20numbers%20that%20are%20not%20related%20to%20a%20specific%20product%20and%20therefore%20I%20just%20equally%20repeat%20the%20number%203%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20pivot%20table%20(which%20is%20set%20to%20present%20the%20months%20as%20lines)%2C%20the%20sum%20of%20this%20repetition%20gives%20me%20the%20obvious%20result%20of%20a%20x3%20multiplication%2C%20however%20I%20would%20like%20to%20receive%20only%20the%20number%20itself%2C%20but%20without%20setting%20the%20results%20to%20%E2%80%9Ccount%E2%80%9D%2C%20%E2%80%9Cmaximum%E2%80%9D%20or%20%E2%80%9Cminimum%E2%80%9D%2C%20because%20I%20want%20the%20final%20line%20in%20the%20pivot%20table%20to%20give%20me%20the%20sum%20for%20all%20months.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrints%20are%20attached%20to%20this%20description.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109942i9D863143DC4E44D9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22print.JPG%22%20title%3D%22print.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%3C%2FP%3E%3CP%3EGabriel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-481116%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPivot%20tables%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETables%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481990%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325044%22%20target%3D%22_blank%22%3E%40GbiKripka28%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck!%20If%20you're%20interested%20I%20can%20recommend%20some%20great%20courses.%20Let%20me%20know%20if%20you'd%20like%20that.%3C%2FP%3E%3CP%3EIn%20the%20meantime%2C%20if%20you%20want%2C%20you%20can%20use%20the%20file%20that%20I%20built%20for%20you.%20It%20solves%20your%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481973%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481973%22%20slang%3D%22en-US%22%3E%3CP%3EHey%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302344%22%20target%3D%22_blank%22%3E%40Celia_Alves%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20I%20will%20try%20and%20have%20a%20look%20at%20this%20Power%20Pivot%20tool.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481352%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325044%22%20target%3D%22_blank%22%3E%40GbiKripka28%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGabriel%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20already%20here%2C%20that's%20default%20option%20in%20recent%20versions%20of%20Excel.%20However%2C%20creating%20new%20PivotTable%20be%20sure%20what%20setting%20is%20checked%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20375px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109992iC7F15B1A82F73CD1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFor%20the%20existing%20PivotTable%20simplest%20way%20to%20check%20is%20right%20click%20on%20the%20table%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20258px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109995i766B22BA93166283%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BIf%20you%20see%20Add%20Measure%20you%20work%20with%20the%20data%20model.%3C%2FP%3E%0A%3CP%3EIf%20not%2C%20click%20on%20More%20tables%20and%20Yes%20after%20that%20to%20add%20your%20existing%20data%20to%20data%20model.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20458px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109997i7F690C05471F6B40%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENew%20PivotTable%20will%20be%20created%20using%20the%20same%20layout%20as%20existing%20one%2C%20however%20you%20may%20lost%20all%20formatting.%20Remove%20old%20one%20and%20move%20new%20on%20the%20desired%20place.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20that%20right%20click%20on%20table%20name%2C%20select%20Add%20measure%20and%20in%20window%20as%20in%20previous%20post%20add%20DAX%20formula%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(ISFILTERED(%5BMonth%5D)%2CAVERAGE(%5BValue%5D)%2CSUM(%5BValue%5D))%3C%2FPRE%3E%0A%3CP%3Eusing%20your%20actual%20column%20names.%20Aggregate%20your%20pivot%20table%20using%20this%20measure%20instead%20of%20the%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481325%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481325%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20assistance.%20How%20can%20I%20%22add%20data%20to%20data%20model%22%20when%20creating%20the%20pivot%20table%3F%20I%20do%20not%20understand%20a%20lot%20from%20pivot%20tables.%20The%20worksheet%20you%20sent%20is%20exactly%20what%20I%20was%20looking%20for%2C%20but%20how%20may%20I%20reproduce%20it%20in%20my%20sheets%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGabriel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481248%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325044%22%20target%3D%22_blank%22%3E%40GbiKripka28%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Gabriel%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20added%20data%20to%20data%20model%20creating%20the%20PivotTable%2C%20you%20may%20add%20measure%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20656px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109972iBE98556D69573AA7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20use%20it%20instead%20of%20the%20column%20pivoting%20the%20table%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481244%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325044%22%20target%3D%22_blank%22%3E%40GbiKripka28%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Ca%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325044%22%3E%40GbiKripka28%3C%2Fa%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOl%C3%A1%20Gabriel!%3CBR%20%2F%3EOne%20good%20solution%20would%20be%20to%20have%20a%20separate%20table%20with%20the%20energy%20and%20water%20consumptions%20per%20month%20(one%20row%20per%20month.)%20Then%20we%20would%20use%20Power%20Pivot%20to%20connect%20those%20two%20tables%20and%20that%20would%20allow%20us%20to%20create%20on%20pivot%20table%20grabbing%20information%20from%20those%20two%20data%20tables.%20I%20am%20leaving%20this%20tip%20here%20in%20case%20you%20want%20to%20explore%20the%20idea%20of%20learning%20Power%20Pivot.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EWithout%20that%20option%2C%20you%20could%20still%20set%20the%20water%20and%20energy%20costs%20on%20a%20separate%20table%20and%20then%20bring%20those%20values%20to%20your%20main%20table%2C%20divided%20by%20the%20number%20of%20products%20you%20have%20each%20month.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFor%20example%2C%20for%20the%20energy%20usage%2C%20th%20formula%20would%20be%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3DSUMIFS(Table2%5BEnergy%20Usage%5D%2CTable2%5BYear%5D%2C%5B%40Year%5D%2CTable2%5BMonth%5D%2C%5B%40Month%5D)%2FCOUNTIFS(%5BYear%5D%2C%5B%40Year%5D%2C%5BMonth%5D%2C%5B%40Month%5D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20table%201%20is%20your%20main%20table%20and%20table%202%20is%20the%20table%20with%20the%20water%20and%20energy%20costs.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109973iCE05E1BD570C4256%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Gabriel.JPG%22%20title%3D%22Gabriel.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPlease%20also%20see%20file%20attached.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20hope%20this%20helps.%3C%2FP%3E%3CP%3EVotos%20de%20um%20dia%20feliz!%20%3A-)%3C%2FP%3E%3C%2FLINGO-BODY%3E
GbiKripka28
Occasional Contributor

Hello,

 

I am facing a problem with data arrangement in a pivot table. When configuring my data base table, the lines are composed of 3 products, while the columns contain information about number, cost etc. One column contains the month, and I have to repeat each month 3 times down to include the 3 products each month. Some other columns involve numbers that are not related to a specific product and therefore I just equally repeat the number 3 times.

 

In the pivot table (which is set to present the months as lines), the sum of this repetition gives me the obvious result of a x3 multiplication, however I would like to receive only the number itself, but without setting the results to “count”, “maximum” or “minimum”, because I want the final line in the pivot table to give me the sum for all months. 

 

Prints are attached to this description.

 

print.JPG

 

Thank you so much

Gabriel

6 Replies

@GbiKripka28 

@GbiKripka28 

Olá Gabriel!
One good solution would be to have a separate table with the energy and water consumptions per month (one row per month.) Then we would use Power Pivot to connect those two tables and that would allow us to create on pivot table grabbing information from those two data tables. I am leaving this tip here in case you want to explore the idea of learning Power Pivot.


Without that option, you could still set the water and energy costs on a separate table and then bring those values to your main table, divided by the number of products you have each month.


For example, for the energy usage, th formula would be:


=SUMIFS(Table2[Energy Usage],Table2[Year],[@Year],Table2[Month],[@Month])/COUNTIFS([Year],[@Year],[Month],[@Month])

 

where table 1 is your main table and table 2 is the table with the water and energy costs.

Gabriel.JPG

Please also see file attached.


I hope this helps.

Votos de um dia feliz! :-)

@GbiKripka28 ,

 

Hi Gabriel,

 

If you added data to data model creating the PivotTable, you may add measure like this

image.png

and use it instead of the column pivoting the table

Highlighted

Hi @Sergei Baklan 

 

Thank you for your assistance. How can I "add data to data model" when creating the pivot table? I do not understand a lot from pivot tables. The worksheet you sent is exactly what I was looking for, but how may I reproduce it in my sheets?

 

Thank you so much

 

Gabriel

@GbiKripka28 

 

Gabriel,

 

Perhaps you already here, that's default option in recent versions of Excel. However, creating new PivotTable be sure what setting is checked

image.png

For the existing PivotTable simplest way to check is right click on the table here

image.png

 If you see Add Measure you work with the data model.

If not, click on More tables and Yes after that to add your existing data to data model.

image.png

New PivotTable will be created using the same layout as existing one, however you may lost all formatting. Remove old one and move new on the desired place.

 

After that right click on table name, select Add measure and in window as in previous post add DAX formula like

=IF(ISFILTERED([Month]),AVERAGE([Value]),SUM([Value]))

using your actual column names. Aggregate your pivot table using this measure instead of the column.

Hey@Celia_Alves 

 

Thank you! I will try and have a look at this Power Pivot tool.

 

:)

@GbiKripka28 

Good luck! If you're interested I can recommend some great courses. Let me know if you'd like that.

In the meantime, if you want, you can use the file that I built for you. It solves your problem.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
33 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies