Old New
64 31 97 64
65 32 98 65
66 33 99 66
67 34 100 67
68 35 101 68
69 36 102 69
70 37 103 70
71 38 104 71
72 39 105 72
73 40 106 73
74 41 107 74
75 42 108 75
76 43 109 76
77 44 110 77
78 45 111 78
79 46 112 79
80 47 113 80
81 48 114 81
82 49 115 82
83 50 116 83
84 51 117 84
85 52 118 85
86 53 119 86
87 54 120 87
88 55 121 88
89 56 122 89
90 57 123 90
91 58 124 91
92 59 125 92
93 60 126 93
94 61 127 94
95 62 128 95
96 63 129 96
130 97
131 98
132 99
133 100
134 101
135 102
136 103
137 104
138 105
139 106
140 107
141 108
142 109
143 110
144 111
145 112
146 113
147 114
148 115
149 116
150 117
151 118
152 119
153 120
154 121
155 122
156 123
157 124
158 125
159 126
160 127
161 128
162 129
163 130
164 131
165 132
166 133
167 134
168 135
169 136
170 137
171 138
172 139
173 140
174 141
175 142
176 143
177 144
178 145
179 146
180 147
181 148
182 149
183 150
184 151
185 152
186 153
187 154
You are given two text files:
The Ch17UnitsTableExample05.txt file is the units fact table:
The Ch17ProductTableExample05.txt file is the product dimension table:
Ex #2 Source Data: Text files.
Other facts: We will use Power Query to import, celan and trasnformat the data.
We will use Power Pivot Data Model, DAX formulas and Relationships on small data set to introduce ourselves to th
Data analysis goal: Create product total sales and total units sold report with visualization.
ata set to introduce ourselves to the tools.
Data type Data Type Icon
Text Decimal number
True/False Currency (Fixed decimal number)
Decimal number Whole number
Fixed decimal number Percentage
Whole number Date/Time
Percentage Date
Date/Time Time
Date Date/Time/Timezone
Time Duration
Date/Time/Timezone Text
Duration True/False
Binary Binary
Any Any
Short Description
Number up to 15 decimals
Number up to 4 decimals
Number with no digit to right of decimal
Number up to 15 decimals with % Number Format
Serial number date and time together
Serial number date
Serial number time
Represents a UTC date/time with a time-zone offset
Serial Number Length of Date and Time
Text
Boolean
File like Excel file or Text file
Sets numbers such as dates and decimals according to regional settings
Product
Aspen Total Sales ($):=SUM(fUnits[Sales ($)])
Quad Total Sales ($):=SUM(fUnits[Sales ($)])
Sunspot Total Sales ($):=SUM(fUnits[Sales ($)])
Grand Total Total Sales ($):=SUM(fUnits[Sales ($)])
Filter Context Makes it possible for SUM function to work in all cells in report:
#REF!
#REF!
#REF!
#REF!
Fact Table = fUnits
< < < < < < < < < < < < < < <
*
ProductID UnitsSold Sales($)
1069 3 89.85
1069 8 239.6
1069 11 329.45
The Measure works across a much
Total Sales ($):=SUM(fUnits[Sales ($)])
=SUM(89.85,239.6,329.45) = 658.9
n all cells in report:
1043 6 287.7
2005 3 65.85
1043 8 383.6
2005 6 131.7
2005 13 285.35
1043 5 239.75
1043 5 239.75
Dimension Table = dProducts
1
ProductID Product FlightRange(M) Price
1069 Aspen 25 29.95
rks across a much smaller range than the original field
1043 Quad 20 47.95
2005 Sunspot 15 21.95
Measure creates Aspen Filter Context
& causes fUnits in 1st argument of
SUMX to be filtered down to only
Aspen rows
Product
Aspen Total Sales OneStep:=SUMX(fUnits,RELATED(dProduct[Price])*fU
Quad Total Sales OneStep:=SUMX(fUnits,RELATED(dProduct[Price])*fU
Sunspot Total Sales OneStep:=SUMX(fUnits,RELATED(dProduct[Price])*fU
Grand Total Total Sales OneStep:=SUMX(fUnits,RELATED(dProduct[Price])*fU
fUnits Table inside SUMX first argument
ProductID Units Sold
1069 3
1069 8
1069 11
its,RELATED(dProduct[Price])*fUnits[Units Sold])
its,RELATED(dProduct[Price])*fUnits[Units Sold])
its,RELATED(dProduct[Price])*fUnits[Units Sold])
its,RELATED(dProduct[Price])*fUnits[Units Sold])
Total Sales OneStep:=S
fUnits Table inside SUMX first argument for Aspen cell
ProductID Units Sold
1069 3 RELATED(dProduct[Price])*fUnits[Units Sold] = 3*29.95 = 89.85
1069 8 RELATED(dProduct[Price])*fUnits[Units Sold] = 8*29.95 = 239.6
1069 11 RELATED(dProduct[Price])*fUnits[Units Sold] = 11*29.95 = 329.45
SUMX creates Row Context for each row in fUnits to
get the values for Price & Units Sold
Total Sales OneStep:=SUMX(fUnits,RELATED(dProduct[Price])*fUnits[Units Sold])
RELATED(dProduct[Price])*fUnits[Units Sold]
RELATED(dProduct[Price])*fUnits[Units Sold]
RELATED(dProduct[Price])*fUnits[Units Sold]
Total Sales OneStep:= 89.85 + 239.6 + 329.45 = 658.9
Total Sales OneStep:= 89.85 + 239.6 + 329.45 = 658.9
89.85 + 239.6 + 329.45
89.85
239.6
329.45
3*29.95 = 89.85
8*29.95 = 239.6
11*29.95 = 329.45
