How to Use the CALCULATE Function in Power BI

Подписаться 22 тыс.

Просмотров 58 тыс.

50% 1

How to Use the CALCULATE Function in Power BI/
In this tutorial, we will learn about the Calculate DAX function in Power BI. Also, we include three different cases where we will apply the CALCULATE function.
Please navigate through the content below:
0:33 Content
1:00 The calculate function
1:31 Syntax
2:23 Two different ways to use calculate
3:28 Cases
Content Link: drive.google.com/file/d/1kASv...
_______________
Highly recommended Power BI books (Affiliate Links):
***The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition: amzn.to/3ldPLpk
***DAX Patterns (Second Edition): amzn.to/3fBKdnD
***Analyzing Data with Power BI and Power Pivot for Excel: amzn.to/3fDPLOd
***Beginning DAX with Power BI: amzn.to/3fDEe1k
***Storytelling with Data: A Data Visualization Guide for Business Professionals: amzn.to/3mfPcwE
***Storytelling with Data: Let's Practice!: amzn.to/36c6Cot
***Information Dashboard Design: amzn.to/2V61o7h
_______________
Relevant PBI Topics
- DAX Basics in Power BI Desktop: • DAX Basics in Power BI...
- MEASURES vs CALCULATED COLUMNS in DAX: • MEASURES vs CALCULATED...
- How to Use the ALL DAX Function in Power BI: • How to Use the ALL DAX...
- How to Use the Filter DAX Function in Power BI [The Basics]: • How to Use the Filter ...
- How to COMBINE Tables in Power BI: • How to COMBINE Tables ...
____________
[DISCLAIMER]: Some links included in this description might be affiliate links. If you purchase a product with those links, I might receive a small commission. However, there is no additional charge to you! I appreciate your support and this will help me provide you with free content on a weekly basis.
If you like the content, feel free to buy me a cup of coffee 😀👇www.paypal.me/vizxlization
Please follow us on:
Web: www.vizxlization.com/
LinkedIn: / vizxlization
Facebook: / vizxlization
Twitter: / vizxlization
Instagram: / nestoradria. .
Leave your comments and questions below!
Don't forget to subscribe!
Thanks so much!
#vizxlization #DAX #CalculateFunction #PowerBI

Опубликовано:

5 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 150

@TheGoodLifePH 4 года назад

Thanks. Just the kind of video training i wanted. Pls continue to create this type of videos. If i may request pls create one for auto refreshing the reports. What ways can we do that especially without using dedicated servers. Thanks

@NestorAdrianzen 4 года назад

Ed D Excellent! Tanks for your suggestions and keep up the great work!

@doriginal69 3 месяца назад

I know how to use this function, but this tutorial taught me exactly how I would have liked someone to teach it to me. Awesome job. Most people just tell you how to do it, but putting in examples of when you would use it and variations is exemplary. Well done.

@NestorAdrianzen 3 месяца назад

Glad to hear that. Cheers!

@sonoftoid8702 2 года назад

Very well done. This video shows you how to drive the CALCULATE car without trying to take you under the hood at the same time. Thanks

@NestorAdrianzen 2 года назад

Sonoftoid, Glad it was helpful! Cheers!👍

@priyadarshinichettiar6750 4 года назад

Thank you so much, I was searching for this query for a long time. Your videos are awesome. Looking forward to more such amazing videos from you. Thanks once again. :)

@NestorAdrianzen 4 года назад

@Priyadarshini, Thanks for your kind words. Keep up the great work!

@just_normal_indian_guy 3 года назад

I have searched for many videos and after that i have found your video. Exact to the point. Thanks man for your help. Appreciate your efforts.

@NestorAdrianzen 3 года назад

Awesome! Glad you found the content helpful.

@benk8279 3 года назад

The first time I understand the calculate function and the formatting of DAX! Thank you:-)!!

@NestorAdrianzen 3 года назад

Great to hear! Keep it up, Ben K.

@waheedabbas2366 3 года назад

one of the best ever explanation with a very good example, perfect, thanks you !!

@NestorAdrianzen 3 года назад

Glad it was helpful! Keep up the great work, Waheed. Cheers!

@virgie1728 3 года назад

You are a good teacher.. sweet and simple, clear, and on point.. luv this👍💖💞

@NestorAdrianzen 3 года назад

Wow, thank you!

@Deeps88 3 года назад

First time I have gone through sensible explanation on RU-vid about power BI. I am new to this tool and comparison you show by applying filter on excel and match the amount that was Awesomeness 👌Sir. THANK YOU PLS UPLOAD MORE video I WOULD tag your content on insta so that more people take most out of it.

@NestorAdrianzen 3 года назад

Appreciate your feedback, Excel with ADeep! Glad it was helpful. Keep it up!

@happyimhealthy 2 года назад

Thank you for the most clear explanation I have watched. I immediately subscribed.

@NestorAdrianzen 2 года назад

Glad to hear that! Cheers 👊

@IvanCortinas_ES 2 года назад

Superb video Nestor. The very best of CALCULATE. Thank you for your examples.

@NestorAdrianzen 2 года назад

Thanks for your support, Iván. Keep up the good work!👍

@123jamalq 4 года назад

You briefed calculate function very smooth way, thanks,

@NestorAdrianzen 4 года назад

Jamal Qamar, I appreciate the feedback. Keep up the great work👍

@irfanabbassi9202 2 года назад

Excellent explanation..thoroughly enjoyed the video...teaching is pure art...Great job

@NestorAdrianzen 2 года назад

I appreciate your feedback, Irfan. Keep it up👍

@tinashechinyati6823 3 года назад

awesome demo on calculate function. enjoyed following through. Thanks

@NestorAdrianzen 3 года назад

Glad it was helpful. Keep it up, Tinashe! 👊

@bonnguyen5433 3 года назад

Great video, easy to follow and understand Thank you for sharing your knowledge.

@NestorAdrianzen 3 года назад

Glad you found it helpful, Non Nguyen. Keep up the good work.👍

@sandraying5990 2 года назад

Thank you so much ! My first DAX experience

@NestorAdrianzen 2 года назад

Great to hear that, Sandra!

@smartshan4u 2 года назад

Very well explained. Great thanks Nestor!

@NestorAdrianzen 2 года назад

Glad it was helpful!

@radhaglasti 3 года назад

Cool man!!! You have simply demonstrated 👍

@NestorAdrianzen 3 года назад

Glad you found it helpful! Cheers!

@zafariqbalsyed6109 3 года назад

Thank you vary much Mr Nestor Adrianzen this is vary helpfull and informative lession good luck

@NestorAdrianzen 3 года назад

Glad it was helpful! Keep up the good work, Zafar. Cheers!

@mr.prasadyadav 2 года назад

Thank you Man, it's very clear cut to understand

@NestorAdrianzen 2 года назад

Glad it helped! Cheers!

@saivishwanathgoud 3 года назад

Awesome ! Thanks for the video and please keep sharing more 🙂.

@NestorAdrianzen 3 года назад

Glad it helped! Keep up the good work, Sai!

@kebincui 3 года назад

Thanks Nestor. Very Clear Tutorial 👍👍👍

@NestorAdrianzen 3 года назад

Glad it was helpful! keep up the good work, Kebin Cui.

@vicmath1005 2 года назад

Thank you. Very helpful explanation of the concept.

@NestorAdrianzen 2 года назад

Glad it was helpful!

@alimatulrahim5226 4 года назад

you are good teacher sir...thanks..

@NestorAdrianzen 4 года назад

I appreciate your feedback, Alimatul. Keep up the good work!

@shakibarad Год назад

You are the best. Thank you so much.

@NestorAdrianzen Год назад

Glad it helped and thanks for your support. Cheers!

@bhanund Год назад

Awesome demo and explanation!

@NestorAdrianzen Год назад

Glad you found the content helpful!

@sravankumar1767 3 года назад

AWESOME, YOUR VIDEOS ARE AMAZING

@NestorAdrianzen 3 года назад

Excellent! Keep up the good work.

@eyitayoeyitayo-lawal2331 Год назад

Brillinat! Thank You

@NestorAdrianzen Год назад

Glad to hear that👍

@mihirthakkar6902 2 года назад

very nicely made. Thanks

@NestorAdrianzen 2 года назад

Thanks for your feedback, Mihir!

@jhamca10 Год назад

What a explanation by you really it's ausume.

@NestorAdrianzen Год назад

Thanks for your support, Chandrashekhar! Keep it up!👍

@ilikedigitallogic5059 3 года назад

This video is very good!!! and Thanks

@NestorAdrianzen 3 года назад

Glad you liked it! Keep it up, I like Digital Logic.

@indianpigeon3680 2 года назад

Thanks Nestor for this excellent video. Please share some interview question which usually ask in interview. thanks

@NestorAdrianzen 2 года назад

Glad it was helpful, Indian Pigeon! I’ll keep in mind your suggestion. Thanks 🙏

@liladhardudhabale3227 2 года назад

Nice Explanation

@NestorAdrianzen 2 года назад

Thanks for you support, Liladhar. Keep it up 🆙

@islam48ify 4 года назад

Wow thanks sir

@NestorAdrianzen 4 года назад

Keep up the great work, Mohand!

@simmisahay2518 3 года назад

Excellent

@NestorAdrianzen 3 года назад

Thank you! Cheers!

@mansijswarnkar2183 Год назад

tTHANK YOU !

@NestorAdrianzen Год назад

You are very welcome!

@anjim8507 3 года назад

Post more DAX videos, really helpfull to me

@NestorAdrianzen 3 года назад

Thanks for stopping by Anji! Keep up the good work!

@anjim8507 3 года назад

Your way of explanation, making notes and teaching by step by steps are awesome....

@NestorAdrianzen 3 года назад

Glad you found it helpful 👊

@DatawithHimanshu Год назад

Great

@NestorAdrianzen Год назад

Glad to hear that. Cheers!

@gogo14882 3 года назад

great

@NestorAdrianzen 3 года назад

Glad it was helpful. Cheers!

@sudippandit1 3 года назад

Awesome video! Can you please upload or link of datasets under this video??

@NestorAdrianzen 3 года назад

Sudip, Thanks for following our content. We usually share the link content in the video description of each tutorial. Here it is as well. Cheers! drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing

@harshalpatel555 3 года назад

Thanks Nestor the video was very knowledgeable. I have one question, When i am drwing a date claim column to axis of stacked column chart .its not filtering according to dates. And also in the report section i am unable to get a date symbol before date claim as you have

@NestorAdrianzen 3 года назад

Harshal, Thanks for stopping by. I highly recommend to create a calendar table and create a relationship based on the date column with the Claims table. After that, use the date column from the calendar table as part of the axis of any chart that you are building. For your second question, you might want to go to the "DATA" section of your report, then "Column Tools" and select the right Date Type. I hope it helps. Cheers! This tutorial about CALENDAR tables might also help: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-IU_2rkTFrLY.html

@raftersteve 3 года назад

thank you, I'm new to this and hope this video will end up being my power BI foundation using it to build financial statements. I've followed your steps using my data which is formatted by department, by month and and amount. In trying to filter for just Dept 1. I get the correct sum for that dept 1, however the Dept1's name is blank and every other department is listed with the Dept 1's cost. Using a matrix view. If I add additional departments using || Dept[DeptName] = "dept 2")) I get the aggregate total cost for both departments, this is then displayed as the total cost for each of my departments. Really need a suggestion on what I could be doing wrong.

@NestorAdrianzen 3 года назад

Steve, Thanks for stopping by. I'm not sure if followed the whole story here, but it seems that it's related to the "context filter" with the CALCULATE function. Remember that the logical operator || takes one option or the other. Whereas, for the logical operator &&, both options need to be correct. Also if you want to keep active some filters, perhaps this tutorial can help. Cheers! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-s_9TYgTRsXY.html

@yourinternationalexperienc2332 3 года назад

Great content! I just have a question - is it possible to have multiple calculations in one measure, based on different values? So for example, I may want to do a sum if city is Nashville, and a count if city is Phoenix. How do I apply this in one measure? Thank you!

@NestorAdrianzen 3 года назад

Your International Experience, Great question! Yes, it is possible. You might want to use a nested IF function to do so. See below an example: Measure = IF( VALUES(Claim[City]) ="Nashville", SUM(Claim[Claim Cost]), IF(VALUES(Claim[City])="Phoenix", COUNTROWS(Claim) ) )

@yourinternationalexperienc2332 3 года назад

Thank you very much for your prompt response!

@KalaiVanan_mskvanan 3 года назад

Brother kindly continue dax we are waiting

@NestorAdrianzen 3 года назад

Will do!

@kostaszogo21 2 года назад

Great video as always ! I have a question though: Is there a way to filter by using only part of the value and not the whole word? For example instead of finding the Cost for [City] ="Nashville", can we find the Cost for every [City] that contains the "*ville" somewhere in its name?

@NestorAdrianzen 2 года назад

Yes, you can! One of the filters of the CALCULATE function can be like the following: CONTAINSSTRING(Claim [City], "*ville") This tutorial will help: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Y82DHwlxAkI.html

@raghun2636 3 года назад

Hi Nestor..where do i find the claims data sample..can you please share?

@NestorAdrianzen 3 года назад

Raghu N, Thanks for stopping by. I usually share the content link in the video description. Here is the link for reference. drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing

@J_G 3 года назад

was alsomost quitting power BI until i watched this video. i still have challenges on how to compare daily revenue figures against monthly targets

@NestorAdrianzen 3 года назад

I appreciate your feedback, Justin. Keep up the good work. Cheers!

@mysticalpotato2110 2 года назад

As you have used All function in the Calculate Filter function, how are the filters working in the visuals?

@NestorAdrianzen 2 года назад

Vinayak, Thanks for stopping by. The ALL function will remove any filter available within a column or table.

@kimmy3047 Год назад

Hi I know that this might be a bit late commenting on an old video but how do I show still the whole Sales Table but a specific customer has a filter context based on a column? like say in your example Making a Table showing The Claim Costs of all the Cities but I want the claim cost under the city name "Knoxville" to be filtered by only the claim costs under the delivery company "FedEx" reflecting a table that has all the claim costs of each city but one of them is filtered

@NestorAdrianzen Год назад

Kimmy, Thanks for stopping by. I think you are trying to add multiple filters to your calculation. The example below might help: Measure:= CALCULATE( [Total Claim Cost], Claim[City] = "Knoxville" && Claims[Delivery Company] = "FedEx" )

@juancardenas84 2 года назад

Hi! How could you sum columns of different tables?

@NestorAdrianzen 2 года назад

Juan, Thanks for your support. What you can do is to create relationships between tables and then create a measure to sum numeric fields from another table. I hope this helps. Cheers!

@pieterjdw 3 года назад

Very informative, but why do you prefer the filter method on the ‘left’. Looks more complicated

@NestorAdrianzen 3 года назад

Good question! I used the FILTER function to apply complex filter conditions. Also, this video was recorded in 2020 when the new CALCULATE filters feature wasn't available yet. You might want to check the following tutorial out to learn about this new feature. I hope it helps. Cheers! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-KDnkGraX5c8.html

@sravankumar1767 3 года назад

Can we use" In operator " for multiples cities apart from OR operator

@NestorAdrianzen 3 года назад

Yes, you can also use "IN". See below as reference. FILTER( ALL(Claim[City]), Claim[City] in {"Nashville","Phoenix","Greenville"} )

@sravankumar1767 3 года назад

@@NestorAdrianzen Thank u very much

@simmisahay2518 3 года назад

Is calculate function always return single value like aggregate function ?

@NestorAdrianzen 3 года назад

Simmi, Thanks for checking the content. As far as I know, yes, the CALCULATE returns a single value. If you want to get a table, you might want to use CALCULATETABLE. Thanks!

@bidhanry9740 2 года назад

i have a question, can you please explain the way dax evaluates, like in the first eg we have CALCULATE( expression, FILTER(ALL(table, exp))), so which exprssion gets evaluate first, what it returns, how the result gets interpreted inside brackets. I hope u get it.

@NestorAdrianzen 2 года назад

Bidhan, Thanks for your question. CALCULATE computes the filter argument first and then the expression. For this example, the filter argument is a table that uses the FILTER function. The ALL function scans all the values in a specific column. I hope this helps! Cheers!

@sangyjap5656 3 года назад

Why can't I choose other data except the just created data(Total Claim Cost). Is it possible to select another data column as well?

@NestorAdrianzen 3 года назад

Sangy Jap. Thanks for stopping by. The first part of the calculate function should be any expression. For the example, we use a measure that adds all the claim cost. However, you can also add other type of measure/calculation. This must be an expression, it cannot be just a numeric column. I hope this solves your question.

@centugurdag7776 Год назад

great content , but why it is difficult /impossible to download the data file ?

@NestorAdrianzen Год назад

Thanks for stopping by. Sorry to hear that. You might want to refresh your browser.

@kusalamarasena3882 3 года назад

i write the below function to get total of the bank sector Bank sales = CALCULATE([sales],FILTER(Orders,Orders[Manger]="9BNK")) here i did not happen to use ALL function. but it worked. here [sales] is a measure. could you please explain this

@NestorAdrianzen 3 года назад

Kusal, Thanks for stopping by. We usually use ALL as a filter modifier to remove all filters from a column or table. Initially, if that table or column doesn't present any filters (usually used in a slicer), the result could be the same by using or not using the ALL function. I hope it helps.

@kusalamarasena3882 3 года назад

@@NestorAdrianzen got it.thanks

@FahriKerimov 3 года назад

I am new to Power BI, coming from long xls experience. What I dont understand is the usage of rows (Shift+Enter) while typing the DAX formulas. What is the purpose of splitting the code in rows and then tabbing? is it just esthetics? or there is functionality for that???

@NestorAdrianzen 3 года назад

Fah Kem, Thanks for stopping by. These are good questions. By doing so, we can improve readibility of the DAX code. Also, if we make mistakes, it is easier to find and fix them. I hope it helps! Cheers!

@FahriKerimov 3 года назад

@Nestor Adrianzen Thanks Nestor for quick response. May I ask you a bit more. I assume the readibility for ourselves not the machines right? I kind of understand that mistakes are highlighted by red colod code on the right of the row, but why we need to use Tab then? I am tottally puzzled after seing at 12:20 of your tutorial paranthesis placed on different rows and on different tab positions, and I cannot think of a potential mistake for paranthesis that one could make. Hope you can help me with my doubts.

@NestorAdrianzen 3 года назад

Yeah. I see your point. In reality there is no standard to write DAX code in Power BI. However, when we create more complex formulas, it’s better not to write them in just one line, because sooner than later we will get lost. You might want to check the following website for reference. Cheers! www.daxformatter.com

@powerbinareal 4 года назад

Obrigado pelo conteúdo! Pode me meu canal também? Sou do Brasil.

@NestorAdrianzen 4 года назад

Power BI na Real, Excellent! Keep up the good work.

@davidjames3494 3 года назад

Why did you not use && instead of || when selecting the 3 cities?

@NestorAdrianzen 3 года назад

Thanks for stopping by, David. The && is an AND operator and for the example, I wanted to use an OR operator. I could have used IN in the filter argument as well. For example: Claim[city] IN {"Nashville", "Phoenix", "Greenville"}

@jayshete5219 4 года назад

"And right here" is a good comment.

@NestorAdrianzen 4 года назад

Jay, I hope you are enjoying the content. Cheers!

@vikramsingh-ws2nn 3 года назад

Hi Nestor, could you please help me as I am trying to get percentage month wise for leaver, but outcome is coming in descending order that is incorrect. I have three tables First is a transaction table(Base Data), second is a fact table with starter, leaver date and status(ALL Employee), last one is a calendar table which is having just date, month, year etc(calendar date Table). Fact tables are having relationship with transaction table. The DAX, which I wrote is leaver count from fact table and divide by total number of employees. But the answer is wrong. (Leaver Count = CALCULATE(COUNT('ALL Employee'[Leaver Info]), FILTER('ALL Employee','ALL Employee'[Leaver Info]="Leaver"))) (HeadCount = DISTINCTCOUNT('Base data'[Employee Code]))

@NestorAdrianzen 3 года назад

Vikram Singh, Thanks for stopping by. For percentage calculations, you might want to check the following tutorial out: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9IdFq2aPxmY.html. If you are getting the right results, but the order is incorrect, you will need to add a "month" # column to your calendar table. Then sort the date column (e.g., the date column that is part of the axis of your visual) by using the "Sort by column" option and select the new column #. Finally, sort by selecting ascending or descending from your visual. I hope it helps! Cheers!

@vikramsingh-ws2nn 3 года назад

@@NestorAdrianzen Thank you so much for this help

@eswarsonu1115 3 года назад

Hi , Need your help. I want to create a column with month name + number of days in a month. Number of days in a month should change when we select month and year from the filter. Some cases we have leap year in that February month should show 29 and like wise non leap year February month should show 28 days. Output should be in this way . Jan 31 Feb 28 Mar 31 Apr 30 When I select year as 2020 from filter output should be like this Jan 31 Feb 29 Mar 31 Apr 30. Thanks in advance.

@NestorAdrianzen 3 года назад

Eswar Sonu, Thanks for taking a look at the content. My suggestion would be to create a Calendar table. The date column will have its own hierarchy [Year, Quarter, Month and Day] that you can use to filter any date. This tutorial will help you create a calendar table. I hope it helps. Cheers! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-IU_2rkTFrLY.html

@AmitErandole Год назад

The link to the downloadable file is broken. Could you fix it please?

@NestorAdrianzen Год назад

Amit, Thanks for letting me know. I just updated the link. Here you go! drive.google.com/file/d/1kASvVjUiWeGSkZPamop5omITg7OuQ8VB/view?usp=sharing

@prateekganguly4119 3 года назад

Could you please share this excel sheet

@NestorAdrianzen 3 года назад

Prateek, Thanks for following our content. Please see the link below for the files. You can also find the link in the video description for every lesson. drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing

@centugurdag7776 Год назад

sorry for my previous message, i got the file

@NestorAdrianzen Год назад

Great. Keep up the great work 👍