You have the following tables in a data model. You create a PivotTable to display SaleAmount by Month. A sample of the results are shown in the following table. You need to ensure that the data appears in chronological order. What should you do?
A.
In the data model, modify the Sort By Column setting for Date[Month Year]
B.
From PivotTable Fields, add Date[Year] to the Rows area
C.
In the data model, modify the Sort by Column setting for Sales[Date]
D.
From PivotTable Fields, modify the Field Settings for Date[Month Year]
TESTED. None of the "solutions" will fully reach the goal (i.e. strict "Chronological Order" is not possible). C is not even an option, as others explained before (it is only possible to sort by columns from the same table). D is far from being the solution as no sort options are available, at least in Excel 2016 which I used for testing. B would break the desired table layout and besides it will not reach strict chronological order. And finally with A the best that can be achieved (by sorting by Year) is that all the 2015 rows appear before 2016's and so on. But there is no way to sort the months so that, for example, Feb shows before Apr. Sorting by Date[Date] is not possible (an error shows because date values are not unique for each monthYear value). So the best choice here is A. A real full solution would be to add a yyyymm calculated column to the model and sort by it.
filtering and sorting in the datamodel is NOT reflected in th epivot table, so A and C are irrelevant. D is correct; we somehow need to modify the current [MontYrar] into a proper hierarchy.
The correct answer is A. To get a chronological order, the column used (Date[Month Year]) should be sorted with another column, so you should do "In the data model, modify the Sort By Column setting for Date[Month Year]"
You can only 'sort by other column' within the same table. Sales[Date] has no date sorting alternative, Date[Month Year] on the other hand has, eg. Date [Month Year ID]. A is correct
This section is not available anymore. Please use the main Exam Page.70-779 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
excelll
Highly Voted 5 years, 8 months agoHiJaak
5 years, 8 months agoAnetaK
5 years, 6 months agoamar111
5 years, 2 months agoFrdFrd
4 years, 5 months agomohroshdy
Highly Voted 5 years, 7 months agoguido_007
Most Recent 4 years, 8 months agoOsamaAL
4 years, 8 months agoCDL
4 years, 8 months agomssql
4 years, 8 months agopanal
4 years, 11 months agor8d1
4 years, 11 months agoabtsoares
4 years, 11 months agoOutsider
5 years agoammu
5 years agoCibrevN
5 years agoJideID
5 years, 1 month agoJideID
5 years, 1 month agoJideID
5 years, 1 month agoamar111
5 years, 2 months agoJrestrepo
5 years, 2 months agoprokocim
5 years, 4 months agonose
5 years, 4 months agoOlivia
5 years, 4 months ago