Excel pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-1377379%22%20slang%3D%22en-US%22%3EExcel%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377379%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20issue%20in%20excel%20pivot%20table%2C%20where%20in%20if%20i%20insert%20a%20calculated%20field%20in%20excel%2C%20the%20formula%20appilied%20to%20all%20individual%20field%2C%20but%20the%20it%20doesnt%20get%20applied%20to%20the%20grand%20total%20section.%20Is%20there%20a%20way%20to%20apply%20it%20to%20the%20grand%20total%20column.%20Because%20of%20this%20it%20gives%20absurd%20results%20like%20summing%20up%20of%20percentages%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1377379%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377545%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377545%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662199%22%20target%3D%22_blank%22%3E%40shai123%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20show%20your%20problem%20with%20the%20example%2C%20It%20will%20be%20more%20easier%20to%20solve%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377669%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377669%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F630219%22%20target%3D%22_blank%22%3E%40Vinit_Joshi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDear%20Vinit%3A%3C%2FP%3E%3CP%3EIn%20the%20%25%20yes%20column%20which%20i%20have%20introduced%20as%20calculated%20field%20in%20Pivot%20the%20grand%20total%20%25%20cell%20totals%20up%20all%20percentage%20above%20it%2C%20whereas%20what%20i%20want%20is%20a%20percentage%20of%20140%2F130%20%3D%20108%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2264.07263294422827%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%22111px%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%22111px%22%3ENo%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%22111px%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%22111px%22%3E(%25%20yes)%20-%20introduced%20as%20calculated%20field%20in%20pivot%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3ESujay%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E20%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E30%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E150%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3EVishak%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E50%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E125%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3EGanesh%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E70%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E60%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E85%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3EGrand%20Total%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E130%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E140%3C%2FTD%3E%3CTD%20width%3D%2216.666666666666668%25%22%20height%3D%2230px%22%3E355%25%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377743%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662199%22%20target%3D%22_blank%22%3E%40shai123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20slightly%20depends%20on%20structure%20of%20your%20source%20and%20how%20you%20calculate%2C%20but%20in%20any%20case%20for%20the%20calculated%20field%20if%20use%20any%20aggregation%2C%20not%20sum%20by%20default%2C%20Total%20will%20be%20incorrect.%20Better%20to%20use%20measure.%20For%20such%20simple%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20478px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190431iA14B174F778507CA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eadd%20data%20to%20data%20model%20creating%20the%20PivotTable%2C%20and%20create%20measure%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20521px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190432i7397B41166C745B6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt%20returns%20desired%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have an issue in excel pivot table, where in if i insert a calculated field in excel, the formula appilied to all individual field, but the it doesnt get applied to the grand total section. Is there a way to apply it to the grand total column. Because of this it gives absurd results like summing up of percentages

3 Replies
Highlighted

Hi @shai123 ,

 

can you show your problem with the example, It will be more easier to solve the problem.

 

Highlighted

@Vinit_Joshi 

 

Dear Vinit:

In the % yes column which i have introduced as calculated field in Pivot the grand total % cell totals up all percentage above it, whereas what i want is a percentage of 140/130 = 108%

 

NameNoYes(% yes) - introduced as calculated field in pivot
Sujay2030150%
Vishak4050125%
Ganesh706085%
Grand Total130140355%
Highlighted

@shai123 

It slightly depends on structure of your source and how you calculate, but in any case for the calculated field if use any aggregation, not sum by default, Total will be incorrect. Better to use measure. For such simple model

image.png

add data to data model creating the PivotTable, and create measure as

image.png

It returns desired result.