measures & calculations in power bi
TRANSCRIPT
![Page 1: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/1.jpg)
Measures & Calculations in Power BI
August 26, 2021
![Page 2: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/2.jpg)
To Receive CPE Credit• Individuals
Participate in entire webinar Answer polls when they are provided
• Groups Group leader is the person who registered & logged on to the webinar Answer polls when they are provided Complete group attendance form Group leader sign bottom of form Submit group attendance form to [email protected] within 24 hours of webinar
• If all eligibility requirements are met, each participant will be emailed their CPE certificate within 15 business days of webinar. Due to the large volume of certificates of completion issued, requests to reissue lost or misplaced certificates will be honored up to 60 days following the webinar
![Page 3: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/3.jpg)
INTRODUCTIONS
Charles Allen Senior Managing Consultant• More than 30 years of experience • Solver Certified• 10-time Microsoft GP MVP
![Page 4: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/4.jpg)
Agenda
• Introduction to DAX
• Measures
• Calculated Columns
• Calculated Tables
![Page 5: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/5.jpg)
Introduction to DAX
![Page 6: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/6.jpg)
Introduction to DAX
• What is DAX?
• It is a collection of functions, operators, & constants that can be used in a formula or expression to calculate & return one or more values
• https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-quickstart-learn-dax-basics
![Page 7: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/7.jpg)
Introduction to DAX
• It is also known as function language, where the full code is kept inside a function
• DAX Basics in Power BI
• https://www.tutorialspoint.com/power_bi/dax_basics_in_power_bi.htm
![Page 8: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/8.jpg)
Introduction to DAX• There are more than 200
DAX functions• Used for measures,
calculated columns, & calculated tables
• Available since 2010• Similarity to Excel formulas• Works on Tables instead of
Cells• References columns instead
of cell addresses
![Page 9: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/9.jpg)
Introduction to DAXLike what?
Revenue =
CALCULATE(
SUM('Module General Ledger'[monthly amount]),
'Account'[accountcategory] IN { "Revenue" }
)*‐1
![Page 10: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/10.jpg)
Introduction to DAXRevenue YoY % =DIVIDE(
[Revenue]
‐ CALCULATE( [Revenue],
‐ SAMEPERIODLASTYEAR('Date'[Date]) ),
CALCULATE( [Revenue],
SAMEPERIODLASTYEAR('Date'[Date])
)
)
![Page 11: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/11.jpg)
![Page 12: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/12.jpg)
![Page 13: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/13.jpg)
Introduction to DAX
Quick MeasuresQuick MeasuresAggregate per Category
Filters
Time Intelligence
Totals
Mathematical Operations
![Page 14: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/14.jpg)
Creating Quick Measures
![Page 15: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/15.jpg)
Quick Measures
What is a Quick Measure?
It’s a measure that’s really fast!
No, it’s not!So, what is it really?
It’s a way to create common measures, like …
Aggregate per category
Filters
Time Intelligence
![Page 16: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/16.jpg)
Quick Measures
How do I create a Quick Measure?
From the Fields area, right-click in the area & select New quick measure
![Page 17: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/17.jpg)
Quick Measures
• Select a calculation
• Select the fields for the calculation
![Page 18: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/18.jpg)
Quick Measures
• Note: This is where measure upon measure comes in handy
• To create a measure for a category, you may need a measure that filters for that category
![Page 19: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/19.jpg)
This Photo by Unknown Author is licensed under CC BY-NC-ND
![Page 20: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/20.jpg)
Creating Measures
![Page 21: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/21.jpg)
Creating Measures• Creating measures create model measures
• Write measures for every calculation if you can
• Only gets calculated when it is used
• Works in memory
• Good for calculating amounts at the report level
• Used for aggregating data
Measures versus Calculated Columns – (1.5)https://www.youtube.com/watch?v=nJSXty9Y4tM
![Page 22: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/22.jpg)
Creating Measures
• Right-click in the Fields area
• Select New measure
![Page 23: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/23.jpg)
This Photo by Unknown Author is licensed under CC BY-NC-ND
![Page 24: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/24.jpg)
Creating Calculated Columns
![Page 25: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/25.jpg)
Calculated Columns• Stored in the data model
• Take up space
• Calculated when the data is refreshed
• Used for slicing & dicing (filtering)
Measures versus calculated columns in DAX & Power BI
https://www.youtube.com/watch?v=ePPi1LLX0sA
![Page 26: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/26.jpg)
This Photo by Unknown Author is licensed under CC BY-NC-ND
![Page 27: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/27.jpg)
Resources
• Power BI – https://powerbi.microsoft.com/en-us/
• Guy in a Cube –https://www.youtube.com/channel/UCFp1vaKzpfvoGai0vE5VJ0w
• Power BI & Solver – Overview (Solver Academy) –https://learn.solvertraining.com/power-bi-overview
• Using Microsoft Power BI (Solver Academy) –https://learn.solvertraining.com/using-microsoft-power-bi
• Enterprise DNA (YouTube) –https://www.youtube.com/channel/UCy2rBgj4M1tzK-urTZ28zcA
• SQLBI – https://www.youtube.com/user/sqlbitv
![Page 28: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/28.jpg)
![Page 29: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/29.jpg)
BKDTECHNOLOGIES SUPPORT CENTER
• BKD Technologies Support Center for Microsoft Dynamics GP 877.253.7778 (toll-free) [email protected] Monday–Friday, 8 a.m.–5 p.m.
![Page 30: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/30.jpg)
BKD, LLP is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website www.nasbaregistry.org
The information contained in these slides is presented by professionals for your information only & is not to be considered as legal advice. Applying specific information to your situation requires careful consideration of facts & circumstances. Consult your BKD advisor or legal counsel before acting on any matters covered
![Page 31: Measures & Calculations in Power BI](https://reader031.vdocuments.us/reader031/viewer/2022012018/615c21965557637cf56e742f/html5/thumbnails/31.jpg)
CPE Credit
• CPE credit will be awarded upon verification of participant attendance
• For questions, concerns, or comments regarding CPE credit, please email BKD Learning & Development Department at [email protected]