Exam 70-779 topic 1 question 64 discussion

Actual exam question from Microsoft's 70-779

Question #: 64
Topic #: 1

HOTSPOT -
You have a model that contains data relating to corporate profits. The model contains a measure named Profit.
You need to create a PivotTable to display the Profit measure in three different formats by using the Show Value As feature. The PivotTable must produce the results shown in the following table.

How should you configure the Show Value As feature for % Profit of Annual Total and % profit of Grand Total? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Reference:
https://www.contextures.com/xlPivot10.html
https://support.office.com/en-us/article/calculate-a-percentage-for-subtotals-in-a-pivottable-6aa66bce-11c5-47f7-b6b6-0959b0b13a72

by yizhang1984 at June 16, 2020, 8:15 p.m.

Comments

Submit Cancel

yizhang1984

Highly Voted 5 years ago

correct!

upvoted 7 times

...

k9999

Most Recent 4 years, 8 months ago

Why the 1st one is not % of Parent Column Total?

upvoted 2 times

jason69

4 years, 5 months ago

That would be my question as well.

upvoted 1 times

...

FrdFrd

4 years, 5 months ago

I agree that Parent Column Total produces the desired result in this scenario, but that is because there is no dimension placed at the Columns bucket (we have only year and month at Rows bucket and 3 times the Profit measure at Values bucket) . If there was one dimension at Columns bucket, say Category or whatever, the percentage would be calculated as cell value divided by the total value of that dimension, which would yield different percentages than we are asked. That is why "% of Parent Total" is the correct answer. It calculates the percentage relative to the parent level of the hierarchy, that is month profit divided by parent year profit

upvoted 1 times

...