![Excel with Allison](/img/default-banner.jpg)
- 17
- 17 592
Excel with Allison
New Zealand
Приєднався 30 гру 2020
Microsoft Certified Trainer and Power BI MVP.
Meaningful Power BI Report Page Titles with Simple DAX
Selected Value vs ISFILTERED can mean the difference between a good and a great DAX dynamic title in Power BI.
Переглядів: 101
Відео
SharePoint Regional Date Settings
Переглядів 29Рік тому
How to change Regional Date Settings in SharePoint/Teams so that your dates entered into Excel, SharePoint lists, etc are recognised correctly.
Fix Dates with Power Query 'Using Locale'
Переглядів 149Рік тому
Struggling with dates that are in a different regional format to yours? Power Query can fix that with just one function - and there's no coding required.
Fix Dates with Text to Columns
Переглядів 148Рік тому
Tired of fighting with dates that won't format properly? Try this trick.
Copy and Paste DAX Code Power BI Hack
Переглядів 3,8 тис.Рік тому
This video shows you an easy way to update DAX code that you've copied from anywhere to reference the correct tables and columns in your data model.
Power BI Community Solution Deep Dive: N to 1 to N Measures
Переглядів 1472 роки тому
This video is in response to a Power BI community post that has generated lots of great discussion and questions. You can find the original post here: community.powerbi.com/t5/Desktop/Sum-values-in-a-n-1-n-relationshop-without-possible-usage-of/m-p/2110606#M782138 Big thanks to @Applicable88 for the thought provoking question and sample data, and to @TheoC for keeping the discussion going.
Power BI Data Source Update: Olympics to Paralympics
Переглядів 1473 роки тому
Power Query Data Source Settings and Applied Steps make it easy to update existing reports to point to new data sources with the same structure. In this video I explain how to do this and troubleshoot errors.
Data Point Rectangle Select
Переглядів 473 роки тому
A quick demo (no sound) on how I used Data Point Rectangle Select to compare the NZ COVID 19 Community cases right now to the first outbreak last year.
Power BI Mobile App Demo
Переглядів 2343 роки тому
Tokyo 2020 Olympics is underway and I'm using Power BI to display the results. Unfortunately mobile friendly view isn't available for Publish to Web Power BI reports (yet) so I've made a video to demonstrate how to use the Power BI mobile app with reports that have been designed to be mobile friendly.
Olympics Historical
Переглядів 1213 роки тому
In this video I use historical Olympic medals stats to demonstrate how to use Power BI, but you can apply the techniques to many data sources and industries. In part one I use parameters and custom functions in Power Query; a great trick for anyone looking to pull data from lots of individual files which all have the same structure. In the second half of the video I go through star schema best ...
Sankey Data Model
Переглядів 5043 роки тому
How model your data to add tiers to a flat data model Sankey diagram
Sankey Demo
Переглядів 1,6 тис.3 роки тому
A quick demo of using and formatting the Sankey custom visual in Power BI.
Microsoft 365 Webinar Productivity
Переглядів 403 роки тому
FREE Webinar; 10 am June 25, 2021 New Zealand Time Zone Do less admin and spend more time on the things that matter. In this webinar, we’ll demonstrate some of the new Microsoft 365 apps that can help you work smarter, not harder. You’ll walk away from this webinar with a new appreciation for Microsoft 365: the personal assistant you didn’t know you had.
Timeline Storyteller - Screen Recording
Переглядів 863 роки тому
A short screen recording of Timeline Storyteller custom visual for Power BI.
Excel Webinar Dashboards
Переглядів 853 роки тому
Excel dashboards make data easier to understand, easier to share and easier to turn insight into action. Learn how to gather multiple data sources into engaging, high-level visual summaries with Excel dashboards so you can track and provide stakeholders with on-demand access to their most important KPIs and metrics in a dynamic, easy to understand format that can speed up decision making.
By default sankey chart sort the largest node value in the down, can i sort it to be the largest value in top?
Allison you are heavenly sent !!! Thank you!
Can you share the sample data to use
HI, If we delete the Raw data, and we add new data to the excel data file, will the new data added to the excel file be reflected in this?
Was pulling my hair out over multiple choice question, you did in 1 hour what I was trying to do for the whole day. Thank you, Allison!
Hello, thank you for your video! I have a similar problem. I have 3 tables: Prices[articlenum], [date], [price]; Articles[articlenum]; Quantity[product], [articlenum], [quantity]. The relation between the tables are also n:1:n. So the context is that products are built out of different articles with different quantities. And the prices of the articles are different during time. So i want to measure the costs of a product per month in the past for example. Now the problem for me is that I don't know how to multiply the costs through [price] and [quantity] by a measure. It is a similar problem like the one you are talking about in this video, but I think the problem in my case is that the value depends on two columns (either [articlenum] and [date] or [product] and [articlenum]) which are not present in both tables([product] only in table quantity and [date] only in table prices). Do I need an extra table? I tried to calculate it in a database of MC Access, there it is no problem and it gives me the costs per product per month. But I want it in Power BI... I am currently using a temporary solution in which I created a new table through a lot of merging queries. I basically put everything in one table. Like: tempTable[product], [articlenum], [date], [price], [quantity]. So here i am able to calculate the costs by just creating an extra column. Works fine, but the loading time of updating the data takes up to 10 min. I think its because of all the merging queries. Thats pretty annoying. Can you help me out with this problem? If you are willing to help, I'd be very thankful and I'd create a test database for you if you want. :)
I think you're right - it should be the same as in this video. Trick is just to pick which table you want to start with, then the other measure will be simple. For example, if you take the Facttable = Prices, ManufacturingTime = Quantity, DimMaterial = Articles, and dimProcessCode = DimProduct (a new table you'll want to create) Here's the measure: Prep Time Many to Many = VAR _ProcessCode = VALUES(dimProcessCode[ProcessCode]) RETURN SUMX(Facttable, CALCULATE( SUMX( FILTER(ManufacturingTime , ManufacturingTime[ProcessCode] IN _ProcessCode ) , ManufacturingTime[PrepTime] ) ) ) You should also use a DimDate table if you don't have one already excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html Then just multiply by SUM(Price) or AVERAGE(Price) or MAX(Price) and use the DimProduct and DimDate in your visuals. Hope that helps?
@@excelwithallison Thank you soo much, it is finally working! :-) I just couldn't find a solution for this problem... finally! I already had the dim tables that you mentioned. I just wanted to keep the context as simple as possible for my question. I was able to edit the majority of my other measures refering to this cost measure now.👍 But in two cases I still have some problems with editing this measure for other calculations. I do get how your measure is working but its kinda hard for me to edit it... 1. Problem: How do I calculate average, min, max costs per date and per product with this measure?😅 2. Problem: I have another table in which the articles are catogoriesed into groups. So its basically another dimtable for articles (or refering to your vid - a dimtable for the Filtertable). And articles then has an extra referring coloumn to the ne group dim table . How do I calculate the costs per group by also filtering date and product again?
Okay i found a solution for the first problem. So only the second one😄
I also solved the second problem. I was able to solve both problems by using SUMMARIZE I can just tell you again, this video helped me out very well. Also thank you so much for replying even tho this video is already a year old. Keep up the good work!👍
@ScharaN so glad you found it helpful, even a year on. There's also AVERAGEX, MAXX, MINX that might be helpful too if you didn't want to use SUMMARIZE but if it's working that's fantastic!
Absolutely amazing video!! 👌loved the guide to visualise Likert data and trick to save the questionnaire. Thanks so much!!!!🙏
So glad you liked it - thanks for the feedback and you're very welcome.
how did you arrange the base data in excel? I cannot figure out how to get multi level sankey.
Great question - I really struggled with that the first time too. Here's a video showing how I did it: ua-cam.com/video/4ruZ4Xf4_fc/v-deo.html
Allison, thank you so much for the video, I learned a lot! 😁 I'm new to Power BI and I'm dealing with many survey forms at once (whose questions have been changing over time ). It's challenging to create a data modeling in this scenario. Would you recommend me any books or some resource about that? Once more, thank you!
Hi~ Glad you learned a lot from the video. I don't have any resources specific to survey forms that change; it is definitely a challenging data model to work with! It depends how you want to treat the change in questions - is it still valid to compare the data once the question has changed? If so you may want to research 'slowly changing dimensions' and see if that helps at all.
Thanks - very nice 👍🏼
You're very welcome
I've done Forms surveys for years and have only consumed Power BI (as a viewer), not designer, and finding this video was a godsend, as I was able to produce most of the visualizations* for an important study AND publish the report to Power BI web workbook for interactive consumption. The one area I'd like to see covered (somewhere) is getting the AI-driven text analytics (from Power Query) working for long form text responses. I didn't have the Premium Per User Capacity licensing that's required, so the function throws an error. It would be nice to see that working. Anyway, I agree with other comments below re: this being super helpful.
So glad you found it useful. Yes the premium licensing for the AI text analytics is a bit of a road block for many. I have written a blog post with screenshots of the process but I'll add it to my list of future post ideas and see if I can't do a decent video demo. In the meantime here's the link to the blog post: excelwithallison.blogspot.com/2021/06/text-analytics-in-power-bi.html
How to make multiple-choice answers as one line node in the Sankey diagram?
It's all about getting the format right in Power Query. What is the source and what is the destination? If your multiple choice answers are one node, what goes in the next node?
multiple-choices answers in one line node in Sankey diagram?
Can you provide a bit more info around your source data?
Super useful, absolutely excellent content ! Thanks so much, I've learned a ton !
You're very welcome!
This was super helpful :) At the 20 minute mark can you think of a way to put the apps into several columns and have the IDs in rows below (either as a list or cells with T/F for each ID)? I am trying to solve very similar problem. Thank you so much!
This could be done using a matrix and a bit of DAX magic to get the T/F. Otherwise just put the ID in rows, the app in columns, and count the ID from the apps used table in values.
this is great thanks
You're welcome!
How do we do benchmarking with previous survey data results?
Hi Jessica~ As long as you haven't changed the questions from the previous survey results, you can do benchmarking or even trend analysis. In this case a Date Dimension table will be necessary to help filter the different survey collection periods. You may wish to add a column to your Date Dimension table that specifies which date ranges apply to each distribution of survey results, or just group by month, year, etc. Here's my post on how you can create a Date Dimension table: excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
@@excelwithallison Thank you Allison for your help!
Hi Allison. This was a very good presentation and I have learned a few tricks. Thanks. I have one question to ask for your advice. Why do we need to use the Excel data's web path to build the Power BI? Why cannot we use Excel data saved in the Sharepoint or One Drive directly?
Hi! Sorry for the late reply. Using the web path ensures that Power BI can access the file from PowerBI.com. In that way, we can auto refresh the data using our Microsoft login credentials.
can you assist with the M query used for the Dim Date
Here's a link to the DimDate table I use for most of my training videos: excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Is it possible to create charts that include the full likert scale response options even if there are no values for some of the rating points (so we depict the full range for each item, which helps with contrasting the charts)?
Hi Michael~ This is possible if you have a matrix or data table with a list of all the possible responses. I'm doing a talk on this next month at the Power BI Summit: globalpowerbisummit.com/
this is good!! 👍
buen video saludos desde Peru
thank you beautiful girl
Thank you for simplifying MS Office! So many ideas now on what to do! Thank you!
You're very welcome, let us know what exciting things you do with it and how you get on!
This was by far the most helpful thing I’ve come across when building power bi with survey data. The multiple choice results has been driving me CRAZY! Thank you!!
This was extremely helpful!!!!! Thanks for helping me not pull my hair out lol. Being completely honest. Quick question where do I find the link for the report?
Glad you enjoyed it Amanda! Here's a link to the report and survey data: excelwithallisonmvp.sharepoint.com/:f:/s/ExcelwithAllison/EtBwTVl8p3JHgbMFK2NzgmgBcC8u2Fg2TXhIlEblM8HiRA?e=pChiVJ And here's the link to fill in the form with your own results: forms.office.com/Pages/ResponsePage.aspx?id=y2WOCy7cTESo4BgvKn-HXI8xPYz2TbdKoCHooCjExWFURUQyN08zOVBQWExBQ05VVTBUOFdSMzhDNyQlQCN0PWcu I update the dataset daily so you can see your results in the report the following day. I will also add this info to the video description.
Excellent tutorial
Glad you liked it
Very interesting, just what I was looking for ... One small question: what is the purpose of the questions table?
Hi Carine~ We didn't really use the DimQuestions table in this particular survey example since we asked so many different types of questions. In some scenarios or datasets though it can be really helpful to help filter your data by Question. This would be helpful if you unpivot the raw data so that you have a column for 'Question' and a column for 'Answer' (similar to what we did in the video with the Likert Responses). It works really well with audits and inspections where each question is a different requirement and you want to be able to analyze them all together. Hope that helps? Allison