• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to divide all items in a column by an item included in the same column then add results to new column|Power Query|Excel Forum|My Online Training Hub

You are here: Home / How to divide all items in a column by an item included in the same column then add results to new column|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryHow to divide all items in a column…
sp_PrintTopic sp_TopicIcon
How to divide all items in a column by an item included in the same column then add results to new column
Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 38
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
1
November 11, 2022 - 7:49 am
sp_Permalink sp_Print

Hi,

I am doing the analysis for some numbers within the balance sheet financial statement manually.

I've included the sample files, one includes the query, and the other one is without it.

The current situation is that I am adding an extra column in the source for each year I want to analyze and do the calculation I need.

Each number is divided by TOTAL ASSETS and displayed as a %.

I want to do it automatically in power query.

I've created the query, and unpivot all columns I need, then I split the column, change its type to date, then replace its item inside but do not know how to divide all items for each row in a column by a value within the same column and it is titled TOTAL ASSETS (this item included in the Detailed item in financial statement column).

So how to add a column and add a formula inside each row that divides the value of the cell by the value of TOTAL ASSETS in the same column?

Is it possible to be done?

Thanks

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
November 11, 2022 - 2:38 pm
sp_Permalink sp_Print sp_EditHistory

Hi mgbsher,

Here is your query with a new custom step:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"Company", type text}, {"Financial Statement", type text}, {"Main item in financial statement", type text}, {"Detailed item in financial statement", type text}, {"2016", Int64.Type}, {"2017", Int64.Type}, {"2018", Int64.Type}, {"2019", Int64.Type}, {"2020", Int64.Type}, {"2021", Int64.Type}, {"2016 VerticalAnalysis", type number}, {"2017 VerticalAnalysis", type number}, {"2018 VerticalAnalysis", type number}, {"2019 VerticalAnalysis", type number}, {"2020 VerticalAnalysis", type number}, {"2021 VerticalAnalysis", type number}}),
Unpivot = Table.UnpivotOtherColumns(#"Changed Type", {"No.", "Company", "Financial Statement", "Main item in financial statement", "Detailed item in financial statement"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(Unpivot,{{"Attribute", "Year"}, {"Value", "Amount"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Year", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Year.1", "Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Year.1", type date}, {"Year.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"BalanceAmount",Replacer.ReplaceValue,{"Year.2"}),
Renamed = Table.RenameColumns(#"Replaced Value",{{"Year.1", "Year"}, {"Year.2", "BalanceAmount/ VerticalAnalysis"}}),
Custom1 = Table.FromRecords(Table.TransformRows(Renamed, (rec)=>Record.TransformFields(rec,{{"Amount", each if rec[Detailed item in financial statement]<>"Fixed Assets" then _/(Table.SelectRows(Renamed,(x)=> x[#"BalanceAmount/ VerticalAnalysis"]="BalanceAmount" and x[Detailed item in financial statement]="Fixed Assets" and x[Year]=rec[Year])[Amount]{0}) else _}})))
in
Custom1
Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 38
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
3
November 11, 2022 - 5:45 pm
sp_Permalink sp_Print

Thanks a lot and much appreciated your valuable time helping me.

I've created a detailed video for the case so you can have a better idea.

https://www.youtube.com/watch?.....T2ZwB1XyLE

Thanks a lot.

I hope you may have time to check the video.

Thanks

Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 38
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
4
November 12, 2022 - 5:46 pm
sp_Permalink sp_Print

- later edit - 

I've tried the code with several adjustments but can not get it working.

I am sure it is my mistake as I am still learning the DAX and the M Language.

Please accept my apologies if I said something wrong or impolite.

Thanks,

Mohamed

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
November 15, 2022 - 12:19 am
sp_Permalink sp_Print

Hi,

The last step in the query I provided wrongly refers to Fixed Assets instead of Total Assets as the denominator.

The step should look like:

= Table.FromRecords(Table.TransformRows(Renamed, (rec)=>Record.TransformFields(rec,{{"Amount", each if rec[Detailed item in financial statement]<>"Total  Assets" then _/(Table.SelectRows(Renamed,(x)=> x[#"BalanceAmount/ VerticalAnalysis"]="BalanceAmount" and x[Detailed item in financial statement]="Total  Assets" and x[Year]=rec[Year])[Amount]{0}) else _}})))

Note that the column name contains 2 spaces: "Total  Assets", therefore the errors you got might be related to this column name.

However, power query is not the right place to make such calculations, you should write measures instead in DAX.

Here is an example YOY%: https://carldesouza.com/power-bi-year-over-year-using-sameperiodlastyear/

Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 38
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
6
November 15, 2022 - 4:55 pm
sp_Permalink sp_Print sp_EditHistory

Thanks a lot for your valuable time.

I want to follow your advice.

However, power query is not the right place to make such calculations, you should write measures instead in DAX.

If I followed your advice, then I will not go for power query from the beginning!?

Or do you mean to only do everything in the power query except for the calculation to be done in DAX?

Also, does making a measure require adding data to the data model?

I also do not use Power BI yet, and everything is within Excel only.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
November 16, 2022 - 1:03 am
sp_Permalink sp_Print sp_EditHistory

Power BI has the same tools: Power Query and Power Pivot, however there are some functions in PBI not available in Excel.

The source data should not have totals or manual calculations, I prepared an example of how you should have the source data.

I used PQ only to convert the source data into a tabular structure, from that point I just used DAX to replicate your "Vertical Analysis"

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
November 16, 2022 - 1:26 am
sp_Permalink sp_Print

Just another note:

a P&L report is a complex subject. To create reports based on a chart of accounts multilevel hierarchy is a challenge, especially if you're not familiar with data models.

Here is an article that might bring some light on these challenges:

https://www.daxpatterns.com/pa.....erarchies/

Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 38
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
9
November 27, 2022 - 10:08 pm
sp_Permalink sp_Print

Thanks a lot for your valuable time reading my messages and helping me.

I will follow your advice, my friend.

Thanks 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Riny van Eekelen, YANINA TRIGO
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.