If you have tried to use the default Power BI waterfall chart to recreate your PowerPoint variance bridge but got frustrated because you were not able to show from and to values along with your variances, then you are in luck, because in this video I will show you how you can build any variance … Continue reading Power BI & DAX: How to Make Waterfall Charts Work (showing starting and ending values)
Author: Business Intelligist
Power BI & DAX: How Fast is KEEPFILTERS() Function?
In my first foray in to investigative journalism, I am taking a look at Microsoft's recommendation to use KEEPFILTERS() function where possible to improve performance. (https://docs.microsoft.com/en-us/power-bi/guidance/dax-avoid-avoid-filter-as-filter-argument) I also talk about Microsoft Power BI Guidance – a very good resource for commonsensical Power BI related advice and best practices. (https://docs.microsoft.com/en-us/power-bi/guidance )
Power BI & DAX Tutorial: How to Implement Allocations
If you are in FP&A and trying to understand profitability of your products/business units, you have had to face a challenge of allocating certain corporate/HQ expenses. In this tutorial I go over the steps of implementing allocations in Power BI: Desktop file location: https://bit.ly/3dOUTNV The calculations below are used in the tutorial: Rent = CALCULATE ( SUM ( GL[Amount] ), KEEPFILTERS ( GL[Account] = "Rent" ) ) Amount with Allocation = … Continue reading Power BI & DAX Tutorial: How to Implement Allocations
Power BI and DAX: Three Reasons to use DAX Variables
There are many reasons to use variables in your DAX code. I decided to make a video about the following three: Readability Caching Performance If you find the video to be helpful, you can find the DAX I used in the video below. Net Price = DIVIDE ( Sales[Gross Sales] - Sales[Rebates] - Sales[Discounts], Sales[Units] ) Net Price (var) = --DIVIDE(Sales[Gross Sales] - Sales[Rebates] - Sales[Discounts],Sales[Units]) VAR netSales = Sales[Gross Sales] - Sales[Rebates] - Sales[Discounts] RETURN DIVIDE ( netSales, Sales[Units] ) Avg Product Price = DIVIDE ( SUMX ( FILTER ( ALL ( Sales ), Sales[Product] = EARLIER ( Sales[Product] ) ), Sales[Gross Sales] - Sales[Discounts] - Sales[Rebates] … Continue reading Power BI and DAX: Three Reasons to use DAX Variables
PowerBI: Using Power BI Embedded as Dev/Test Environment for Power BI Premium
Whether "Power BI Pro is a tool for amateurs and Power BI Premium is a tool for pros" is true or not is debatable. What is not debatable, however, is the fact that we need a dev/test environment where we could harden our models without having to affect our production environment. Power BI Premium is … Continue reading PowerBI: Using Power BI Embedded as Dev/Test Environment for Power BI Premium
POWER BI & DAX: How to Flatten Parent Child Hierarchy
Unfortunately, Power BI does not natively support Parent/Child hierarchies. Luckily, we have several DAX functions that make it possible to convert (or flatten) a parent/child hierarchy into a traditional Power BI Level based hierarchy. In this video I introduce parent child hierarchies, talk about some of the requirements necessary for Power BI to be able … Continue reading POWER BI & DAX: How to Flatten Parent Child Hierarchy
Power BI & DAX: Working with Workdays
Working with Workdays does not have to be hard work. At least that's the claim I make in the video below: Please watch the video to understand the theory behind the calculations. I am pasting the calcs below just in case you find them useful. DATE Table calcs: IsHoliday =VAR Holiday = RELATED ( Holidays[Holiday] )RETURN IF ( Holiday = BLANK (), FALSE (), TRUE () ) WeekDayNo =WEEKDAY ( 'Date'[Date], 2 ) IsWorkDay =IF ( 'Date'[IsHoliday] || 'Date'[WeekDayNo] > 5, FALSE (), TRUE () ) WorkDayID =IF ( 'Date'[IsWorkDay], CALCULATE ( COUNT ( 'Date'[Date] ), FILTER ( ALL ( 'Date' ), 'Date'[IsWorkDay] = TRUE () && 'Date'[Date] <= EARLIER ( 'Date'[Date] ) ) )) Measures: NoOfWorkDays =CALCULATE ( COUNT ( 'Date'[Date] ), 'Date'[IsWorkDay] ) Revenue by Work Day =DIVIDE ( [Revenue], [NoOfWorkDays] ) … Continue reading Power BI & DAX: Working with Workdays
COVID19 vs. US Economy (update #2)
In this follow up for the original analysis I used May updates for the US Census indicators. Unfortunately,things are going from bad to worse. Retail is down 15%, while some components of retail are down 40%, 60%, and even 80%. Link to dashboard: https://bit.ly/2RQ2jr0
Power BI: How to Implement Dropdown Navigation
EDIT 2021-03-02: if you would like to learn how to create a Dynamic Dropdown Navigation - please follow this link: https://businessintelligist.com/2021/03/02/dynamic-dropdown-navigation-in-powerbi/ Dropdown navigation allows us to use the real estate of the report page more efficiently. Traditionally, we use buttons and bookmarks to implement navigation between different pages in our reports, however, as number of … Continue reading Power BI: How to Implement Dropdown Navigation
Power BI & DAX: How to Implement Dynamic Page and Chart Titles
We often need to have a dynamic title (page or chart level) that changes based on what the user has selected from the different slicers/filters on the page. In this video, I go over several strategies for accomplishing this task. I start with a very simple one, that requires no knowledge of DAX and then … Continue reading Power BI & DAX: How to Implement Dynamic Page and Chart Titles
business intelligist 