Home

Pivot Tables - show payments per currency

%3CLINGO-SUB%20id%3D%22lingo-sub-815929%22%20slang%3D%22en-US%22%3EPivot%20Tables%20-%20show%20payments%20per%20currency%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815929%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20managed%20to%20find%20the%20total%20amount%20paid%20per%20payment%20method%20using%20Pivot%20Tables%20but%20I%20was%20wondering%20how%20to%20divide%20the%20payments%20methods%20per%20currency%20too%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-815929%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-817705%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%20-%20show%20payments%20per%20currency%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-817705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396228%22%20target%3D%22_blank%22%3E%40adam97%3C%2FA%3E%26nbsp%3BFrom%20the%20PivotTable%20Field%20list%20(the%20sidebar%20of%20all%20your%20fields)%2C%20you%20can%20click%20on%20the%20value%20for%20a%20list%20of%20options%20and%20select%20%22Value%20Field%20Settings%22.%20From%20there%2C%20under%20the%20%22Show%20value%20as%22%20tab%2C%20you%20can%20change%20the%20value%20from%20currency%20to%20a%20calculation%20%22%25%20of%20grand%20total%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20tricky%20part%20i%20have%20found%20is%20if%20you%20want%20to%20show%20the%20total%20amount%20in%20currency%20format%2C%20in%20addition%20to%20the%20%25%20of%20the%20total.%20I%20have%20found%20the%20best%20way%20to%20do%20this%20is%20adding%20your%20data%20to%20a%20data%20model.%20Sounds%20complicated%2C%20but%20super%20easy.%20Try%20the%20following%20steps%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Convert%20your%20data%20to%20a%20table%20(Select%20all%20data%2C%20then%20click%20CTRL%2BT)%3C%2FP%3E%3CP%3E2.%20Create%20Pivot%20Table%20(Insert%20tab%2C%20Pivot%20Table).%20Wen%20doing%20this%2C%20check%20the%20box%20that%20says%20%22add%20this%20data%20to%20the%20Data%20Model%22%3C%2FP%3E%3CP%3E3.%20Add%20your%20total%20amount%20paid%20(or%20whatever%20your%20naming%20convention%20is)%20to%20the%20value%20field%20of%20your%20pivot%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E4.%20Duplicate%20step%204%20(the%20data%20model%20allows%20for%20multiple%20value%20in%20the%20value%20field.%20On%20the%202nd%20value%20you%20add%2C%20click%20on%20the%20value%20and%20select%26nbsp%3B%22Value%20Field%20Settings%22.%26nbsp%3B%3C%2FP%3E%3CP%3E5.%20With%20the%20value%20field%20settings%20tab%20open%2C%20select%20the%20%22Show%20value%20as%22%20tab%20and%20then%20select%20%22%25%20of%20grand%20total%22%20from%20the%20drop-down%20list.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
adam97
Occasional Visitor

Hi,

 

I managed to find the total amount paid per payment method using Pivot Tables but I was wondering how to divide the payments methods per currency too?

 

Thanks

1 Reply
Highlighted

@adam97 From the PivotTable Field list (the sidebar of all your fields), you can click on the value for a list of options and select "Value Field Settings". From there, under the "Show value as" tab, you can change the value from currency to a calculation "% of grand total"

 

The tricky part i have found is if you want to show the total amount in currency format, in addition to the % of the total. I have found the best way to do this is adding your data to a data model. Sounds complicated, but super easy. Try the following steps:

 

1. Convert your data to a table (Select all data, then click CTRL+T)

2. Create Pivot Table (Insert tab, Pivot Table). Wen doing this, check the box that says "add this data to the Data Model"

3. Add your total amount paid (or whatever your naming convention is) to the value field of your pivot table. 

4. Duplicate step 4 (the data model allows for multiple value in the value field. On the 2nd value you add, click on the value and select "Value Field Settings". 

5. With the value field settings tab open, select the "Show value as" tab and then select "% of grand total" from the drop-down list. 

 

Hope this helps. 

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies