Excel Magic Trick 1407: Data Model & DAX Measures for Monthly Totals Report From Two Fact Tables

preview_player
Показать описание
See how to calculate Monthly Totals for Sales from Daily Records and Ad Costs from Month Records using The Data Model, Two Fact Tables with a Relationships to Data Table, and Two DAX Measures. These are the same data sets from Excel Magic Trick 1405, but in this video we learn how to create Report using DAX Formulas.
1. (00:12) Introduction and comparison of formula method done in Excel Magic Trick 1405 and the DAX Formulas used in this video.
2. (02:19) Build Data Table
3. (05:01) Import Tables into Power Pivot Data Model and Build Relationships.
4. (07:06) Build DAX Measures
5. (09:10) Hide Fields From Client Tool
6. (10:00) Start to Build Data Model PivotTable and then Sort Month Name Column in Date Table by Month Number
7. (11:12) Build Report with DAX Measures
8. (11:35) Discussion of Filter Context in a PivotTable and how it works with DAX Measures
9. (12:29) Summary
  1. excelisfun
  2. Excel
  3. Microsoft Excel
  4. Highline College
  5. Mike Girvin
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:12:46

Excel Magic Trick 1407: Data Model & DAX Measures for Monthly Totals Report From Two Fact Tables

Excel Magic Trick 0:05:26

Excel Magic Trick 1405: Monthly Totals Report: Sales from Daily Records, Costs from Monthly Records

Excel Magic Trick 0:28:32

Excel Magic Trick 1387: Data Model & DAX Formulas: Quantity Frequency Distribution by Year &...

Excel Magic Trick 0:03:56

Excel Magic Trick 1392: Case Sensitive Unique Count: Power Query or Data Model PivotTable?

Excel Magic Trick 0:11:22

Excel Magic Trick 1403: DAX Formulas: Explicit or Implicit Measures? Why Explicit is Better.

Excel Magic Trick 0:07:54

Excel Magic Trick 1409: Build PivotTable From 2 Tables without Power Pivot or VLOOKUP

Excel Magic Trick 0:37:16

Excel Magic Trick 1471: Array Formula: Add Daily Rainfall 5 AM to 5 AM Next Day: (Most Automatic)

Excel Magic Trick 0:10:51

Excel Magic Trick 1400: Conditionally Format Row in Class Enrollment Table with Complex Criteria

Excel Magic Trick 0:13:34

Excel Magic Trick 1404: Sales Per Working Day by Month Formulas For Three Year Dynamic Report

Excel Magic Trick 0:04:41

Excel Magic Trick 1404: Sales Per Working Day by Month using Power Query

Excel Magic Trick 0:12:10

Excel Magic Trick 1417 Excel Functions Ranking Unit & Total Profit for Products within Manufactu...

Excel Magic Trick 0:14:01

Excel Magic Trick 1408: DAX Measure: Moving 12 Month Average on Daily Sales (DAX Formatter too)

Excel Magic Trick 0:06:05

Excel Magic Trick 1402: Formula to Put Check Mark In Cell If Number Greater Than Zero

Excel Magic Trick 0:05:23

Excel Magic Trick 1186: Extract Color From Description Field & Create Revenue by Color PivotTabl...

Excel Magic Trick 0:02:49

Excel Magic Trick 1298: Group By Date in Power Pivot Data Model, New in Excel 2016

Excel Magic Trick 0:35:56

Excel Magic Trick 1451: Hack Data Model, Date Table & DAX Measures WITHOUT buying Power Pivot

Excel Magic Trick 0:12:14

Excel Magic Trick 1427: DAX GEOMEANX function: Average Compounding Rate per Period for FV Calc

Excel Magic Trick 0:07:05

Excel Magic Trick 1406: Calculate Moving Average with AVEARGEIFS & EOMONTH Functions

Excel Magic Trick 0:11:17

Excel Magic Trick 1416: Round Up to 15 or 30 Minute Increments for Hours Worked Time Calculations

Excel Magic Trick 0:07:27

Excel Magic Trick 1401: Conditionally Format Row For BOLD Date

Excel Magic Trick 0:05:08

Excel Magic Trick 1189: Extract Color From Description Create Revenue by Color PivotTable, 2nd Take

Excel Magic Trick 0:21:12

Excel Magic Trick 1443: DAX Formula for Sales Per Working Day: Power Pivot or Power BI Desktop

Excel Magic Trick 0:19:53

Excel Magic Trick 1432: 9 DAX Formulas to Build Confidence Intervals CONFIDENCE.T, STDEV.S…

Excel Magic Trick 0:34:41

Excel Magic Trick 1395: DAX Measures for Show Values As % of Grand Total, Column Total, Parent Total

Комментарии
Автор

Hi, I know that my question might be off topic, but do you know a way to append in data model new static scenarios (available in excel table)? I mean you built a working flexible scenario in the data model and you want to have also the historical data in the same place and when you export data via pivot you have it all. Thx

ciprianblaster
Автор

Sir i have fact / sales table in form of months group no dates, only monthly sales, do you think i need calendar table in power bi, if yes how can i create relationships 1 to many or 1 to 1.
Please assist me.

shoaibrehman
Автор

sorry bro but the link to files is not working :)

aliseddik
Автор

right mouse click for the fill series...thanks for the reminder to bring up the menu...!

DougHExcel
Автор

I have stock (stock on hand) pivot. That works live. I can see current stock by refreshing the pivot but i cannot see data of previous days or dats.... Can you help me to solve the problem @excellsfun.

punam
Автор

How do I then get the Variance on the pivot table?

jitnagar
Автор

I found this video very useful as i was trying to make cube from excel sheets.

punam
Автор

Thanks for another great video. To make the date table would it have been easier to use the built-in New Date Table feature in the PowerPivot Design tab or are there disadvantages to using it?

RobMichaels