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
Highlighted
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

@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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies