20 - Actual v Budget Dashboard with CUBEFUNCTIONS in Excel - Bernard Obeng Boateng
ฝัง
- เผยแพร่เมื่อ 25 ก.ค. 2024
- The goal of this session is to show participants how to create a dynamic Actual v Budget Dashboard with CUBEFUNCTIONS. From just two tables of Actual v Budget data, trainees will make use of Power Query, Power Pivot and CUBEFUNCTIONS to convert the Data into a Dynamic Dashboard that can be sliced by Department, Account Type and Scenario.
Files for the session: 1drv.ms/u/s!AsVgPBp9qkOCgeEm_...
This is the best tutorial i found on TH-cam. Thank you so much.
Absolutely brilliant. Your descriptions of each function were very understandable and a special thank you for speaking each key combination. I learned more than CUBEFUNCTION information from your video. Thank you. I am a subscriber!
Thank you for such an outstanding dashboard planning and executing. I found the data preparation very clean and clear and the model you built is sustainable.
This is next level mate. Great job. Thx
Love this dashboard, thank you for the step by step guide
Simple, clear and easy explanations; awesome tutorial. Thank you from Madagascar
This is awesome. Keep up the good job Ben
Good job bro from Ghana ;) Keep pushing...... Thanks for the sharing God beless you
This is really awesome, I'm actually working on budget control. Believe this will be very helpful for me. Thank you for sharing.
obeng you really snapped on this one
also 19:40 explained how to fill blank rows using a neighboring cell better than any tutorial out there.
Really great!- Thank you
Really useful!!! thanks for sharing !
Awesome,, thank you for sharing with us
Excellent work!
Thank you for sharing this! so helpful:)
Excellent one!
Amazing Tutorial. Do more please!
Great Session and thank you for sharing
Amazing Tutorial
Thank you
Excellent, than you for sharing
Great session. I will take some of your ideas and apply it to a dashboard that I need to design which will include Actuals, Budget and Forecast ( Field Estimates ). Thanks for sharing . I still believe cube formulas are very powerful however people don't used them much
Love 💖💖
Great dashboard, thanks for sharing 👍
I have a question though..what if I want to add slicers after I insert the cube function? Can I still do that or do I need to start the process a new ?
@@piero141980 You can insert a new slicer from the data model and update your cube formulas with the slicer name.
Hi Bernard
Is there any reason to create the dimension tables called Scenario, Department and Accounts in the data model? As all these information are in the two fact tables, when you append them it seems always possible to use the slicers when you make the pivot tables. Is that correct? Thank you
We do that (normalize the data) to reduce the amount of data that is unnecessarily repeated in the datasets.(i.e. data redundancy). It improves the performance of your model.
Fantastic webinar on Cube Functions. You referenced providing the "dynamic M language for the date table." I was unable the references. Can you please provide the M Language to create a "dynamic date table?" It would be very helpful and appreciated. Thank you for sharing your Excel knowledge with the global community!
Here you go Mark: = {Number.From(List.Min(Table[Start Date Column]))..Number.From(List.Max(Table[End Date Column]))}
@@FinexSkillsHub Thank you so much for providing this dynamic M formula. I enjoy watching and learning on your TH-cam channel. Love the music & vibe 🎶🎶
@@markp8600 Welcome Mark
This is really a great video and very useful. But if there is any update on both actual and budget sheet do this automatically updated everything including the power query thing?
Thank you so much for sharing this😊
Yes Faustina. The dashboard will be updated when you refresh
I have problem, after clicking quarry it takes huge amount of time and then its close automatically
Please how do I update the data model every month? Kindly share.
Interesting
Great session and thank you so much. Once the dashboard is created, can I go back to actual and budget data and add more rows of data? Will it automatically update on the dashboard?
Yes. Works well if the data is stored in a table in Excel. You can add on data from there and refresh
How can sort by internal and external departments
Dear Obeng,
This is so much helpful! I tried creating the same but I hit a roadblock. I added an additional slicer, say a sub-Department. When I converted the table to formulas, the link between Department and sub-Department was lost. How can I correct this?
You may need to add the name of the new slicer in the CUBEVALUE formula so that it can slice those values. New slicers can be added as an extra expression in existing formulas.
Are you still on TH-cam, I have a question?
Can you send file?
Where is ytd ?
I believe it's Year To Date (The period from the beginning of a fiscal year to the end of a reporting period.)
Bernad why is it that in your actual you are adding sales and expenses?
To get a single value for our measure. This will later be broken down or filtered by the account labels so we will get the total for each account.
Hello -this helps!! can i send you a request on an email. I have a project to discuss. let me know if you can share your email?