Paul has attended:
Excel Advanced - Formulas & Functions course
Power BI Reporting course
Customer Service Excellence course
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
