Monthly tutorials on Microsoft Fabric, Power BI, Excel, Python and SQL. From popular community data & analytics meetup, originally London-based, now online since lockdown.
Thanks for the great efforts, it’s really helpful, yet, I would like to ask what about having more than one company and need to apply filters, I’ve done that but every time I apply the filters, total assets, liabilities, and total equity + liability are always gone as if the filter is being applied to the totals display as well. Thanks for your answer in advance if you can help out
Hey, great vid! any advice as to how to handle the addition of new accounts? I feel like it would be very difficult considering the layout and order stuff
agreed! I sort of found a work around so far but its only been a day since i replicated this so i maybe havent encountered all potential issues haha. I used formulas in my table like this for my sorting for my Primary Key column =IF(ROW()=2, 101, IF([@Account] = OFFSET([@Account], -1, 0), OFFSET([@[Primary Key]], -1, 0), OFFSET([@[Primary Key]], -1, 0) + 1)) I made a primary key column that literally goes from 1 and down until the end, i have my excel tab sorted by the FS order column which i made using this formula so that the income statement is the top always =IF([@[Financial Statement Type]]="Income Statement", 101, IF([@[Financial Statement Type]]="Balance Sheet", 201, IF([@[Financial Statement Type]]="Cash Flow Statement", 301, IF([@[Financial Statement Type]]="Cash Movement", 401, 0)))) then for my levels i did =IF(ROW()=2, 101, IF([@[Level 0]] = OFFSET([@[Level 0]], -1, 0), OFFSET([@[Level 0 Order]], -1, 0), OFFSET([@[Level 0 Order]], -1, 0) + 1)) and so on. These formulas are essentially saying if this rows level 0 is the same/equal to the level 0 cell above it then keep the sort # the same if not add 1. KEY thing though is DO NOT RESORT your data it will mess up your orders to help with that i added a copy and pasted version of the primary key column so if it ever got resorted by accident you have a starting point to resort it and then fix any that got out of order thankfully accounts arent added all that often. SO that being said when you add a new account you can just add a row in where you want it to be sorted so if its cash put it in the middle of your cash accounts or the bottom and those formulas will do their thing to edit the orders below once you add the rest for each level column. Sorry if thats too confusing and probably still not the best solution to make it smoother.
This is very useful. Thank you. Instead of using current month, current quarter, current year, how would you go about choosing a specific period with a starting month and ending month or starting date and ending date?
Thanks Chris, this is excellent and solves several challenges for me carrying out this type of analysis. The one thing that I am struggling with is the sorting of the columns using the level orders. In particular, Level 1 does not work with my database which appears to be because the Level 1 column includes several blanks (same content) which have several different numbers in the Level 1 order column. I cannot find a way to resolve this issue. Can you help me to resolve this? Thanks
I got something to work. I filtered down to literally just one row of data then since it still said i couldnt sort my level 1s to each other i did more googling and i added this SortLevel1 = 'youtablename here'[Level 1 Order] as an added column into the mapping table. Still only one row of data. I then told it to sort my level 1 by this SortLevel1 and it worked i then went and unfiltered everything in powerquery. I did this for each level 1,2,3,4 for my work file. For some reason level 2 it wouldnt let me still so i will try again maybe later but the rest worked. Hope this helps you!!!
Hi there, Could you please explain the sorting for your level 3 order. I don't understand why level 1 and level 2 is sorted in sequence but level 3 skips a few numbers. I have been staring at the sheet for a while now and can't figure it out. Great video BTW!
Great Video! Would love to see how you mark the special variantions and especially how you model other rules for special variances like "9 items in a row ascending/descending".
Amazing Video!!! I am trying to combine the learnings from the video to a table I have in my company, quick question, how would you go about getting the initial share capital (assuming it has not changed in years) so when you take a desired year to filter it gets included?
Hello, i did this model for Qatar 2022 at my office and everyone were really excited for it, now as American Cup is coming I was requested to do another one but with American Cup design. So thank you very much for this video, it helped me a lot wit Power BI.
Thanks for the insightful tutorial, i learnt alot from it. I can't seem to find the next tutorial which is going to focus more on sensitivity analysis, monte carlo analysis, and Power BI visualization. If this has been posted, i would appreciate it if you respond with the link.
Thank you for the great video explanation. This version is easier to learn. Following your steps I made one successful sample in Power BI. Just one question, why here we do not consider subgrouping the raw data? Xbar-R
Hi - Great video - thanks! Am I right in saying the the Azure map visual will replace the shape map visual. If so, is there a way in Azure to create a polygon map based on MSOA's. Using a JSON file?
Great video, but I think there is something missing as the table is incorrect. Wolves and Sheffield Utd should be reversed as Wolves have a better GD than Sheffield Utd. The same goes for West Ham, Watford and Bournemouth. So how do you organise a rank based on Pts being the same to then filter by GD and if GD is the same then by GF?
Hi, I went through the video multiple times but couldn't find how the income statement rollup Excel sheet was getting used in DAX. Have a similar requirement where on the report we need to show positive amounts but rollup should be based on the sign of the parent. Any references are welcome or if someone could point me to the part in the video I might have missed. Thanks
@@dpv343 Maybe I missed something but will look at the video againPBIX file along with other video for chasflow he has shared I just liked the entirety of the solution here why I wanted to take this approach. @LondonBusinessAnalyticsGroup any reference would be helpful if you read this comment.
Hi Chris, another great video, thanks very much. Just one question - in the date table, you have trues and falses in the current month, current quarter and current year columns - am I right to assume this is formula driven?
My categories for some reason always includes a (Blank) option, even though there are no blanks in my dataset and I included no additional rows (or blanks) in the slicer table. How can I get rid of this (Blank) option? Thanks!
