17Q3

Which three actions should you perform in sequence in Power Query Editor?

• Select the [Department] and [Stage] columns and unpivot the other columns

  Rename the [Attribute] column as [School] and the [Value] column as [Score]

  Group by the [Department],[School1],[School2],[School3],[School4] and create a new column named [Avg Score] that uses the AVERAGE function on the [Stage] column

• Select the [Department] and [Stage] columns and unpivot the other columns

  Rename the [Attribute] column as [School] and the [Value] column as [Score]

  Group by the [Department] and [School] and create a new column named [Avg Score] that uses the AVERAGE function on the [Score] column

• Select and unpivot the [Department] and [Stage] columns.

  Rename the [Attribute] column as [School] and the [Value] column as [Score]

  Group by the [Department],[School1],[School2],[School3],[School4] and create a new column named [Avg Score] that uses the AVERAGE function on the [Stage] column

• Select and unpivot the [Department] and [Stage] columns.

  Rename the [Attribute] column as [School] and the [Value] column as [Score]

  Group by the [Department] and [School] and create a new column named [Avg Score] that uses the AVERAGE function on the [Score] column

Answer explanation

Step 1: Select the [Department] and [Stage] columns and unpivot the other columns.We unpivot the School1, School2, School3, and the School4 columns.You might want to unpivot data, sometimes called flattening the data, to put it in a matrix format so that all similar values are in one column.Example:When you unpivot, you unpack the attribute-value pairs that represent an intersection point of the new columns and re-orient them into flattened columns:* Values (in blue on the left) are unpivoted into a new column (in blue on the right).* Attributes (in green on the left) are unpivoted into a new column (in green on the right) and duplicates are correspondingly mapped to the new Values column.Step 2: Rename the [Attribute] column as [School] and the [Value] column as [Score[,Step 3: Group by [Department] and [School] and..Reference:https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

You have a report that contains a bar chart and a column chart. The bar chart shows customer count by customer segment. The column chart shows sales by month.You need to ensure that when a segment is selected in the bar chart, you see which portion of the total sales for the month belongs to the customer segment.How should the visual interactions be set on the column chart when the bar chart is selected?

• highlight

• filter

• no impact

Answer explanation

In most visuals, highlighting doesn't remove the unrelated data. Instead it highlights the related data. The rest of the data remains visible but dimmed.Note: By default, visualizations on a report page can be used to cross-filter and cross-highlight the other visualizations on the page. For example, selecting a state on a map visualization highlights the column chart and filters the line chart to display only data that applies to that one state.Incorrect:Not B: Filters remove all but the data you want to focus on.Reference:https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-reports-filters-and-highlighting

A user creates a Power BI report named ReportA that uses a custom theme. You create a dashboard named DashboardA. You need to ensure that DashboardA uses the custom theme. The solution must minimize development effort. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

• Publish ReportA to Power BI.

• From ReportA save the current theme.

• Publish ReportA to the Microsoft Power BI Community theme gallery.

• From DashboardA, create a custom theme.

• From DashboardA, upload a JSON theme.

Answer explanation

With Power BI Desktop report themes, you can apply design changes to your entire report, such as using corporate colors, changing icon sets, or applying new default visual formatting.When you publish a Power BI Desktop file to the Power BI service, you publish the data in the model to your Power BI workspace. The same is true for any reports you created in Report view.To publish a Power BI Desktop dataset and reports1. In Power BI Desktop, choose File > Publish > Publish to Power BI or select Publish on the ribbon.Sign in to Power BI, if you aren't already signed in.2. Select the destination. You can search your list of available workspaces to find the workspace into which you want to publish. The search box lets you filter your workspaces. Select the workspace, and then click the Select button to publish.

E: One way to create a custom theme is to upload a JSON file that has settings for all the colors you'd like to use for your dashboard. In Power BI Desktop, report creators use JSON files to create themes for reports. You can upload these same JSON files for dashboards, or find and upload JSON files from the Theme gallery page in the Power BI Community.You can also save your custom theme as a JSON file and then share it with other dashboard creators.Reference:https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-upload-desktop-files https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-themes

You need to create a visualization that compares revenue and cost over time. Which type of visualization should you use?

• waterfall chart

• stacked area chart

• line chart

• donut chart

Answer explanation

Line charts can have many different lines, for example both revenue and cost over time.Reference:https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-line-chart

You have a report in Power BI Desktop.You add a key influencers visual as shown in the exhibit. (Click the Exhibit tab.)

Select the answer choice that completes each statement based on the information presented in the graphic.NOTE: Each correct selection is worth one point.

• Identifying additional factors that increase attrition can be achieved by [turning on Cross-report]

  Employee attrition [.2] times greater when employees work overtime.

• Identifying additional factors that increase attrition can be achieved by [turning on Cross-report]

  Employee attrition [3] times greater when employees work overtime.

• Identifying additional factors that increase attrition can be achieved by [adding more fields to expand by]

  Employee attrition [3] times greater when employees work overtime.

• Identifying additional factors that increase attrition can be achieved by [moving fields from Explain by to Expand by]

  Employee attrition [3] times greater when employees work overtime.

Answer explanation

Box 1: moving fields from Explain by to Expand byYou can use Expand By to add fields you want to use for setting the level of the analysis without looking for new influencers.Why do certain factors become influencers or stop being influencers as I move more fields into the Explain by field?The visualization evaluates all explanatory factors together. A factor might be an influencer by itself, but when it's considered with other factors it might not.Box 2: 3 -0.30 instead of 0.10. A factor of 3 greater.Reference:https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

You build a report to help the sales team understand its performance and the drivers of sales. The team needs to have a single visualization to identify which factors affect success. Which type of visualization should you use?

• Key influencers

• Line and clustered column chart

• Q&A

• Funnel chart

Answer explanation

The key influencers visual helps you understand the factors that drive a metric you're interested in. It analyzes your data, ranks the factors that matter, and displays them as key influencers. For example, suppose you want to figure out what influences employee turnover, which is also known as churn. One factor might be employment contract length, and another factor might be commute time.When to use key influencers.The key influencers visual is a great choice if you want to:See which factors affect the metric being analyzed.Contrast the relative importance of these factors. For example, do short-term contracts affect churn more than long-term contracts?Reference:https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

You have a table that contains the following three columns:City -✑ Total Sales✑ OccupationYou need to create a key influencers visualization as shown in the exhibit. (Click the Exhibit tab.)

How should you configure the visualization? To answer, select the appropriate options in the answer area.

• Analyze: City

  Explain by: Total Sales

• Analyze: Total Sales

  Explain by: Occupation

• Analyze: Total Sales

  Explain by: City

• Analyze: Occupation

  Explain by: City

Answer explanation

Box 1: Total Sales -The key influencers visual helps you understand the factors that drive a metric you're interested in, here Total Sales. It analyzes your data, ranks the factors that matter, and displays them as key influencers.Box 2: Occupation -Measures and summarized columns are automatically analyzed at the level of the Explain by fields used.Reference:https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

You are using the key influencers visual to identify which factors affect the quantity of items sold in an order.You add the following fields to the Explain By field:✑ Customer Country✑ Product Category✑ Supplier Country✑ Sales Employee✑ Supplier Name✑ Product Name✑ Customer CityThe key influencers visual returns the results shown in the following exhibit.What can you identify from the visual?

• Customers in Austria order 18.8 more units than the average order quantity.

• Customers in Boise order 20.37 percent more than the average order quantity.

• Product Category positively influences the quantity per order.

• Customers in Cork order lower quantities than average.

Answer explanation

Average quantity of units is displayed.Incorrect:Not B: Average quantity of units is displayed, not percentage.Reference:https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

You have a report that contains four pages. Each page contains slicers for the same four fields. Users report that when they select values in a slicer on one page, the selections are not persisted on other pages. You need to recommend a solution to ensure that users can select a value once to filter the results on all the pages. What are two possible recommendations to achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

• Create a bookmark for each slicer value.

• Sync the slicers across the pages.

• Replace the slicers with page-level filters.

• Replace the slicers with report-level filters.

Answer explanation

: You can sync a slicer and use it on any or all pages in a report.B: You can set filters at three different levels for the report: visual-level, page-level, and report-level.Note: Suppose you want your report readers to be able to look at overall sales metrics, but also highlight performance for individual district managers and different time frames. You could create separate reports or comparative charts. You could add filters in the Filters pane. Or you could use slicers. Slicers are another way of filtering. They narrow the portion of the dataset that is shown in the other report visualizations.Reference:https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers

You have a report that includes a card visualization. You need to apply the following conditional formatting to the card while minimizing design effort: ✑ For values that are greater than or equal to 100, the font of the data label must be dark red. ✑ For values that are less than 100, the font of the data label must be dark gray. Which type of format should you use?

• Color scale

• Rules

• Field value

Answer explanation

Finding the conditional formatting in the card visual is a bit tricky. There is no separate option for that. You need to go to the Format tab of the visual, and then expand the Data Label. The right beside the Data Label's colour you need to hover your mouse, and you will find a three dots icon appearing, which if you click on it, you will see Conditional Formatting.Now in the Conditional Formatting tab, you can apply it in different methods. for example, you can choose Rules, and thenThe Rules mode will give you the ability to put custom roles as below;Reference:https://radacad.com/enhance-the-card-visual-in-power-bi-with-conditional-formatting