如何通過示例使用 Power BI CALCULATE 函數

了解如何使用 Power BI CALCULATE 函數，同時提供如何使用它的示例。

CALCULATE 函數可以說是 Power BI 最重要（也是最流行）的 DAX 函數之一。它易於使用且用途廣泛，可讓您擴展數據分析並開發更有價值的 Power BI 報告。

本教程重點介紹 CALCULATE 函數是什麼以及如何使用它，並假設您已經熟悉Power BI和DAX的基礎知識。

什麼是 Power BI 計算功能？ 

根據 Microsoft Power BI 文檔，CALCULATE 函數構成篩選函數類別的一部分，並被定義為“在修改的篩選上下文中評估表達式”。表達式本質上是一種度量，包括 SUM、AVERAGE 和 COUNT 等函數。此表達式在一個或多個過濾器的上下文中進行評估。 

您可能知道，過濾器也可以應用於 Power BI 報表，只需添加切片器，根本無需使用 CALCULATE 函數創建度量。但是，有許多用例更適合 CALCULATE 函數。將其用作另一個函數的組件特別有用。我們將在下面的示例中看到這是如何計算總數百分比的。

DAX 計算基本語法

CALCULATE 函數的基本DAX 語法是：

CALCULATE( <expression> [, <filter1> [, <filter2> [, ...]]])

由 DATACAMP 工作區復制代碼提供支持

CALCULATE 函數由 2 個關鍵組件組成：

• 表達式-這是聚合組件，就像使用 SUM、AVERAGE 和 COUNT 等函數構建的度量一樣。
• 過濾器- 此組件允許您指定一個或多個控制聚合上下文的過濾器。

CALCULATE 函數中可以使用 3 種類型的過濾器：

• 布爾過濾器表達式- 這是一個簡單的過濾器，其結果必須為 TRUE 或 FALSE。
• 表格過濾器表達式- 這是一個更複雜的過濾器，其結果是一個表格。
• 過濾器修改功能- 諸如 ALL 和 KEEPFILTERS 之類的過濾器屬於此類，它們可以更好地控制您要應用的過濾器上下文。

您可以通過用逗號分隔每個過濾器來將多個過濾器添加到 CALCULATE 函數的過濾器組件。所有過濾器都被一起評估，它們的順序無關緊要。 

您可以使用邏輯運算符控制如何評估過濾器。如果您希望所有條件都被評估為 TRUE，那麼您可以使用 AND (&&)。這也是上面提到的過濾器的默認行為。或者，使用 OR (||) 運算符，必須將至少一個條件評估為 TRUE 才能返回結果。

如何使用 Power BI 計算

要使用 CALCULATE，只需在表中添加一個度量。您可以通過導航到功能區中的建模選項卡並選擇“新測量”來執行此操作。 

下面是一個簡單的 CALCULATE 函數示例，該函數使用 SUM 來查找 Country = United Kingdom 的總收入和過濾。我們將在教程結束時再次更詳細地討論這個示例。

UK Revenue = CALCULATE(SUM('Online Retail'[Revenue]), 

               'Online Retail'[Country] = "United Kingdom")

由 DATACAMP 工作區復制代碼提供支持

在表格中查看時，我們可以看到 UK Revenue 度量只是對 Country 應用了一個過濾器，此外還有針對 Month 的表格中已經存在的過濾器上下文。以這種方式使用 CALCULATE 函數可以讓我們更精細地控制視覺中顯示的信息類型。 

創建度量時要記住的重要一點是遵循良好的數據建模實踐，特別是在查詢的速度和優化方面。因此，CALCULATE 函數的某些用途比其他用途更快或更合適。 

例如，Microsoft 文檔建議您避免將 FILTER 函數用作其他函數的參數（例如在 CALCULATE 函數中）。相反，最好盡可能使用布爾表達式，因為它們已為此目的顯式優化。

在上面的示例中，我們使用布爾表達式來定義 Country = United Kingdom 的過濾器。這是一種更快、更優化的方法。另一方面，這是 FILTER 函數的示例，我們得到相同的結果，但計算速度較慢：

UK Revenue = CALCULATE(SUM('Online Retail'[Revenue]),
              FILTER('Online Retail',
                        'Online Retail'[Country] = "United Kingdom")

由 DATACAMP 工作區復制代碼提供支持

Power BI 計算函數示例

使用現實世界的電子商務數據集，我們將探索 CALCULATE 函數可用於解決業務問題的一些關鍵方法。要學習本教程，您可以從 Datacamp Workspaces訪問電子商務數據集。

該數據集包含有關客戶每次購買的信息：購買的國家、產品描述、購買日期和時間，以及購買的每種產品的數量和價格。

我們將在 CALCULATE 函數的幫助下回答以下問題：

• 與所有其他國家/地區相比，英國的月總收入如何？
• 總收入的百分之幾來自英國？
• 每天的累計收入是多少？

與所有其他國家/地區相比，英國的月總收入如何？

要回答這個問題，我們需要使用 CALCULATE 函數創建兩個度量。首先，我們使用一個簡單的布爾過濾器來創建一個返回英國總收入的度量（使用 SUM 函數）：

UK Revenue = CALCULATE(SUM('Online Retail'[Revenue]),
              'Online Retail'[Country] = "United Kingdom")

由 DATACAMP 工作區復制代碼提供支持

接下來，我們創建一個類似的度量，但這次我們使用 FILTER 函數。此過濾器表達式遍歷 Country 列的每一行，並返回一個包含與過濾條件匹配的行的表。此處需要 FILTER 函數，因為我們無法從過濾器返回簡單的 TRUE 或 FALSE。相反，我們得到一個包含多個值的表。

Non-UK Revenue = CALCULATE(SUM('Online Retail'[Revenue]),
                    FILTER('Online Retail',
                        'Online Retail'[Country] <>"United Kingdom")

由 DATACAMP 工作區復制代碼提供支持

因此，我們可以看到，與所有其他國家/地區相比，英國占該電子商務商店收入的大部分。

英國占總收入的百分比是多少？

這是 Power BI 開發人員和用戶面臨的一個非常常見的問題類型，它是應用 CALCULATE 函數的完美案例。

為了找到總數的百分比，我們首先需要能夠返回總數，而不會受到報告中其他過濾器上下文的影響。為此，我們使用稱為 ALL 函數的過濾器修飾符。使用此函數，我們指定我們希望我們的計算完全忽略任何過濾器的列。 

在此示例中，我們正在尋找英國總收入的百分比。這意味著我們的計算應該忽略 Country 列中的任何過濾器。

Total Revenue = CALCULATE(SUM('Online Retail'[Revenue]),
                  ALL('Online Retail'[Country]))

由 DATACAMP 工作區復制代碼提供支持

這是相關的，因為 CALCULATE 可以從已經過濾 Product[Color] 的過濾器上下文中執行。在這種情況下，ALL 的存在意味著 Product[Color] 上的外部過濾器被忽略並替換為 CALCULATE 引入的新過濾器。如果我們不是按品牌切片，而是按矩陣中的顏色切片，這一點就很明顯了。

現在我們知道了我們的總收入，我們可以構建一個度量來顯示總收入的百分比。然而，由於我們對英國特別感興趣，我們將再次使用 CALCULATE 函數，但這次我們將使用一個簡單的布爾過濾器。

UK % of Revenue = CALCULATE(SUM('Online Retail'[Revenue])/[Total Revenue],
                    'Online Retail'[Country] = "United Kingdom")

由 DATACAMP 工作區復制代碼提供支持

現在我們可以看到，英國占這家電子商務商店總收入的 84%。

每天的累計收入是多少？

累積收入可以讓我們深入了解收入趨勢。通過在圖表上繪製此累積收入，我們還可以直觀地查看收入是否隨著時間的推移以更快的速度增長。

要回答這個問題，我們必須使用 CALCULATE 函數以及以下過濾函數創建度量：ALLSELECTED、FILTER 和使用 MAX 函數的評估。

Cumulative Revenue = CALCULATE(SUM('Online Retail'[Revenue]),
                FILTER( ALLSELECTED('Online Retail'[InvoiceDate]),
                'Online Retail'[InvoiceDate] <= MAX('Online Retail'[InvoiceDate])))

由 DATACAMP 工作區復制代碼提供支持

讓我們來看看為什麼這些過濾器在這裡很重要：

• FILTER 函數允許對我們指定的 2 個過濾器中的每一個進行逐行評估，並在匹配的情況下返回一個表。
• ALLSELECTED 函數在當前查詢中重置 InvoiceDate 上的過濾器（在當前查詢下方的結果中是折線圖），同時仍然允許外部過濾器（例如來自切片器）。
• MAX 函數用作評估的一部分——我們希望對查詢中當前日期或以下的所有日期的收入求和。

閉幕致辭

在本教程中，我們討論了 Power BI 中的 CALCULATE 函數是什麼以及如何使用它。我們還將 CALCULATE 函數應用於現實世界的電子商務數據集，並用它來回答一些關鍵的業務問題。

CALCULATE 是 Power BI 中最有用的功能之一，您可能需要在構建報告和生成對數據的更深入見解時經常使用它。 

希望本教程不僅可以幫助您了解 CALCULATE 的基本語法，還可以幫助您了解它與作為Power BI 數據分析師解決實際業務問題的關係。 

鏈接：https ://www.datacamp.com/tutorial/power-bi-calculate-tutorial

#powerbi

What is GEEK

Buddha Community

Microsoft Power BI Consulting | Power BI Solutions in India

Hire top dedicated Mirosoft power BI consultants from ValueCoders who aim at leveraging their potential to address organizational challenges for large-scale data storage and seamless processing.

We have a team of dedicated power BI consultants who help start-ups, SMEs, and enterprises to analyse business data and get useful insights.

What are you waiting for? Contact us now!

No Freelancers, 100% Own Staff
Experienced Consultants
Continuous Monitoring
Lean Processes, Agile Mindset
Non-Disclosure Agreement
Up To 2X Less Time

##power bi service #power bi consultant #power bi consultants #power bi consulting #power bi developer #power bi development

Hire Power BI Developer | Microsoft Power BI consultants in India

Hire our expert Power BI consultants to make the most out of your business data. Our power bi developers have deep knowledge in Microsoft Power BI data modeling, structuring, and analysis. 16+ Yrs exp | 2500+ Clients| 450+ Team

Visit Website - https://www.valuecoders.com/hire-developers/hire-power-bi-developer-consultants

#power bi service #power bi consultant #power bi consultants #power bi consulting #power bi developer #power bi consulting services

Is Power BI Actually Useful?

The short answer, for most of you, is no. However, the complexity and capability of the products could be beneficial depending on what type of position or organization you work in.

In my effort to answer this common question about Power BI I researched the following:
– Power BI Desktop Gateway
– Syncing on-prem SQL server data
– Syncing SharePoint Online list data
– Syncing data from an Excel workbook
– Building, and sharing a dashboard
– Inserting a Power BI visualization into PowerPoint

To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training

The feature spread above gave me the opportunity to explore the main features of Power BI which break down as:
– Ingesting data, building a data set
– Creating dashboard or reports with visualizations based on that data

In a nutshell Power BI is a simple concept. You take a data set, and build visualizations that answer questions about that data. For example, how many products have we sold in Category A in the last month? Quarter? Year? Power BI is especially powerful when drilling up or down in time scale.
And there are some interesting ways to visualize that data:
However, there are a number of drawbacks to the current product that prevented me from being able to fold these visualizations into our existing business processes.

1. Integration with PowerPoint is not free. This shocked me.

The most inspiring Power BI demo I saw at a Microsoft event showed a beautiful globe visualization within a PowerPoint presentation. It rendered flawlessly within PowerPoint and was a beautiful, interactive way to explore a geographically disparate data set. I was able to derive conclusions about the sales data displayed without having to look at an old, boring chart.

During the demo, nothing was mentioned about the technology required to make this embedded chart a reality. After looking into the PowerPoint integration I learned that not only was the add-in built by a third party, it was not free, and when I signed up for a free trial the add-in could barely render my Power BI visualization. The data drill up/down functionality was non-existent and not all of the visualizations were supported. Learn more from Power bi online course

2. Only Dashboards can be shared with other users, and cannot be embedded in our organization’s community on SharePoint.

Folks in our organization spent 50% of their time in Outlook, and the rest in SharePoint, OneNote, Excel, Word, and the other applications needed for producing documents, and other work. Adding yet another destination to that list to check on how something is doing was impossible for us. Habits are extremely hard to change, and I see that consistently in our client’s organizations as well.

Because I was not able to fold in the visualizations with the PowerPoint decks we use during meetings, I had to stop presentations in the middle, navigate to Internet Explorer (because the visualizations only render well in that browser), and then go back to PowerPoint once we were done looking at the dashboard.

This broke up the flow of our meetings, and led to more distractions. I also followed up with coworkers after meetings to see if they ever visited the dashboard themselves at their desk. None of them had ever navigated to a dashboard outside of a meeting.

3. The visualizations aren’t actually that great.

Creating visualizations that cover such a wide variety of data sets is difficult. But, the Excel team has been working on this problem for over 15 years. When I import my SharePoint or SQL data to Excel I’m able to create extremely customized Pivot Tables and Charts that show precisely the data I need to see.

I was never able to replicate visualizations from Excel in Power BI, to produce the types of visualizations I actually needed. Excel has the ability to do conditional formatting, and other customizations in charts and tables that is simply not possible with Power BI. Because of how generic the charts are, and the limited customization it looks “cool” without being functional.

In conclusion, if you have spare time and want to explore Power BI for your organization you should. However, if you are seriously thinking about how you can fold this product into your work processes, challenge yourself to build a dashboard and look at it once a week. See if you can keep that up for a month, and then think about how that change affected your work habits and whether the data analysis actually contributed value each time. At least half of you will realize that this gimmicky product is fancy, but not actually useful.

Take your career to new heights of success with Power BI online training Hyderabad

#power bi training #power bi course #learn power bi #power bi online training #microsoft power bi training #power bi online course

Power BI vs Tableau

In your search for a Business Intelligence (BI) or data visualization tool, you have probably come across the two front-runners in the category: Power BI and Tableau. They are very similar products, and you have to look quite closely to figure out which product might work the best for you. I work for Encore Business Solutions; a systems partner that specializes in both Power BI and Tableau. We’ve seen more than a few scenarios in which Tableau was being used when the company really should have gone with Power BI, and vice-versa. That was part of the inspiration for this side-by-side comparison.

Unfortunately, the internet is full of auto-generated and biased pages regarding which product trumps the other. The truth is, the best product depends more on you, your organization, your budget, and your intended use case than the tools themselves. It is easy to nit-pick at features like the coding language that supports advanced analysis, or the type of maps supported — but these have a minimal impact for most businesses. I’m going to do my best to stay away from these types of comparisons.

To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training

In writing this comparison, I did a lot of research. The result was more than just this article: I also created a tool that can generate a recommendation for you based on your response to a short questionnaire. It will generate a score for both Power BI and Tableau, plus provide a few other things to think about.

Tableau Software
Founded in 2003, Tableau has been the gold-standard in data visualization for a long time. They went public in 2013, and they still probably have the edge on functionality over Power BI, thanks to their 10-year head start. There are a few factors that will heavily tip the scales in favour of Tableau, which I’ll cover in the next few paragraphs.

Tableau: Key Strengths
Let’s make one thing clear from the start: if you want the cream of the crop, all other factors aside, Tableau is the choice for you. Their organization has been dedicated to data visualization for over a decade and the results show in several areas: particularly product usability, Tableau’s community, product support, and flexible deployment options. The range of visualizations, user interface layout, visualization sharing, and intuitive data exploration capabilities also have an edge on Power BI. Tableau offers much more flexibility when it comes to designing your dashboards. From my own experience, Tableau’s functionality from an end-user perspective is much farther ahead of Power BI than the Gartner Magic Quadrant (below) would have you believe.

Tableau built their product on the philosophy of “seeing and exploring” data. This means that Tableau is engineered to create interactive visuals. Tableau’s product capabilities have been implemented in such a way that the user should be able to ask a question of their data, and receive an answer almost immediately by manipulating the tools available to them. I have heard of cases in which Tableau actually declined to pursue the business of a customer in the scenario that the customer didn’t have the right vision for how their software would be used. If you just want something to generate reports, Tableau is overkill.

Tableau is also much more flexible in its deployment than Power BI. You can install the Tableau server in any Window box without installing the SQL server. Power BI is less flexible which I will discuss in Power BI Weaknesses.

Tableau can be purchased on a subscription license and then installed either in the cloud or an on-premise server.

Finally, Tableau is all-in on data visualization, and they have their fingers firmly on the pulse of the data visualization community’s most pressing desires. You can expect significant future improvements in terms of performance when loading large datasets, new visualization options, and added ETL functions.

Tableau Weaknesses
Unfortunately, Tableau comes at a cost. When it comes to the investment required to purchase and implement Tableau – 9 times out of 10 it will be more expensive than Power BI, by a fair margin. Often, Tableau projects are accompanied by data-warehouse-building endeavours, which compound the amount of money it takes to get going. The results from building a data warehouse and then hooking up Tableau are phenomenal, but you’ll need an implementation budget of at the very least $50k – plus the incremental cost of Tableau licenses. Learn more from Power bi online course

Of course, a data warehouse is not a requirement. Tableau connects to more systems out-of-the-box than Power BI. However, Tableau users report connecting to fewer data sources than most other competing tools. Overall, considering the investment required to implement a data warehouse is a worthy indicator of the commitment required to get the most out of Tableau.

Power BI
Power BI is Microsoft’s data visualization option. It was debuted in 2013, and has since quickly gained ground on Tableau. When you look at Gartner’s most recent BI Magic Quadrant, you’ll notice that Microsoft is basically equal to Tableau in terms of functionality, but strongly outpaces Tableau when it comes to “completeness of vision”. Indeed, the biggest advantage of Power BI is that it is embedded within the greater Microsoft stack, which contributes to Microsoft’s strong position in the Quadrant.

Power BI: Key Strengths
Though Tableau is still regarded by many in the industry as the gold standard, Power BI is nothing to scoff at. Power BI is basically comparable to all of Tableau’s bells and whistles; unless you care deeply about the manifestation and execution of small features, you’re likely to find that Power BI is fully adequate for your BI needs.

As I mentioned, one of the biggest selling points of Power BI is that it is deeply entrenched in the Microsoft stack – and quickly becoming more integrated. It’s included in Office 365, and Microsoft really encourages the use of Power BI for visualizing data from their other cloud services. Power BI is also very capable of connecting to your external sources.

Because Power BI was originally a mostly Excel-driven product; and because the first to adopt Microsoft products are often more technical users, My personal experience is that Power BI is especially suitable for creating and displaying basic dashboards and reports. My own executive team really likes being able to access KPIs from the Office portal, without having to put much time into the report’s creation, sharing, and interactivity.

Power BI’s biggest strength; however, is its rock-bottom cost and fantastic value. For a product that is totally comparable to the category leader, it’s free (included in Office 365) for basic use and $10/user/month for a “Pro” license. This increases adoption of the product as individuals can use Power BI risk-free. For companies that don’t have the budget for a large Business Intelligence project (including a data warehouse, dedicated analysts, and several months of implementation time), Power BI is extremely attractive. Companies that are preparing to “invest” in BI are more likely to add Tableau to their list of strongly considered options.

Power BI is available on a SaaS model and on-premise; on-premise is only supported by Power BI Premium licensing.

Microsoft is also investing heavily in Power BI, and they’re closing the small gaps in their functionality extremely fast. All of those little issues some users have with Power BI are going to disappear sooner rather than later.

Power BI Weaknesses
As I’ve mentioned, Tableau still has the slight edge on Power BI when it comes to the minutiae of product functionality; mostly due to their 10-year head start. But perhaps Power BI’s greatest weakness is its lack of deployment flexibility. For Power BI on-premise you need to install the Power BI Report Server as well as the SQL Server.

I also mentioned that Tableau works well for users with large amounts of data and for users that want on-premise systems. You should be aware that there are some new features being added to Power BI via Power BI Premium that help catch Microsoft up to Tableau in the areas of large datasets and on-premise capabilities – but Power BI Premium adds significant cost, and these features are relatively new. Tableau still reigns in these areas.

To get more knowledge of Power BI and its usage in the practical way one can opt for Power bi online training Hyderabad from various platforms. Getting this knowledge from industry experts like IT Guru may help to visualize the future graphically. It will enhance skills and pave the way for a great future.

#power bi training #power bi course #learn power bi #power bi online training #microsoft power bi training #power bi online course

Power BI In Brief – 2020

Every month, we bring you news, tips, and expert opinions on Power BI? Do you want to tap into the power of Power BI? Ask the Power BI experts at ArcherPoint.

Power BI Desktop – Feature List
More exciting updates for August—as always:

• Reporting - Perspectives support for Personalize visuals; rectangular lasso-select for data points; additional dynamic formatting support to more visuals
• Analytics - Direct Query support for Q&A
• Visualizations - Linear Gauge by xViz; advanced Pie & Donut by xViz; ratings visual by TME AG; toggle switch by TME AG; fdrill down Pie PRO by MAQ Software; ADWISE RoadMap; updates to ArcGIS Maps; extending Admin capabilities for AppSource visuals
• Template Apps - Agile CRM analytics for Dynamics 365
• **Data Preparation ** - Text/CSV By Example
• Data connectivity - Cherwell connector; Automation Anywhere connector; Acterys connector

To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training

Power BI Developer Update
And the updates continue—this time, for developers:

• Updates in embedded analytics
• Automation & life-cycle management
• New API for updating paginated reports data sources
• Get dataset/s APIs return new additional properties
• Embed capabilities
• Persistent filters support for embedding in the organization
• Phased embedding
• Control focus behavior for create/clone visual
• Additional Javascript API enhancements
• Selected learning resources

Multiple Data Lakes Support For Power BI Dataflows
And if that’s not enough, Microsoft also announced improvements and enhancements to Azure Data Lake Storage Gen2 support inside Dataflows in Power BI. Improvements and enhancements include: Support for workspace admins to bring their own ADLS Gen2 accounts; improvements to the Dataflows connector; take-ownership support for dataflows using ADLS Gen2; minor improvements to detaching from ADLS Gen2. Changes will start rolling out during the week of August 10. Read more on multiple data lakes support in Power BI dataflows.

To get more knowledge of Power BI and its usage in the practical way one can opt for Power bi online training Hyderabad from various platforms. Getting this knowledge from industry experts like IT Guru may help to visualize the future graphically. It will enhance skills and pave the way for a great future.

#power bi training #power bi course #learn power bi #power bi online training #microsoft power bi training #power bi online course