Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

power query pivot

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Power Query / Pivot

Power Query / Pivot

ResolvedVersion 365

Power Query / Pivot

Hi, Complicated to explain but will try. I have a power query which loads in each month data, and combines the data. I want to see the % of each line entry

I.e. I want to see something like this in a final power pivot outputed from the combined data

ID Number In Out %
10 500 630 126%
20 750 800 107%
30 800 1600 200%
40 50 30 60%

However, I get the totals in and out, but I cannot get the % correct? If I load results into a pivot, it just adds the % from each data source. I guess I need to create a measure or something? Thanks for any help.

EG data for id 10 is as follows...

ID Number In Out %
Jan 200 200 100%
Feb 75 60 80%
Mar 225 370 164%

Final display is....
ID Number In Out %
10 500 630 344%

(Dummy data used)

RE: Power Query / Pivot

Hi Paul,

Thank you for the forum question.

You will need to do a DAX measure in Power Pivot to get what you want.

Try:

Percentage of in out:=Divide(Sum(Out),Sum(In),0)

Add this measure in Power Pivot and format the result as %. If you then add the measure to the Pivot Table, you get what you want.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 14 Mar 2022: Automatically marked as resolved.

Excel tip:

Separate the year from a date

To separate the year from a date use the =year() function, eg a date is in cell A1 and in A2 you wish to display the year enter the function =year(A1)

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.