You need to create a DAX measure that will return the percent of total sales for each product. The measure must respect the report-level filter when calculating the total. How should you complete…

These two questions test knowledge of DAX filter context manipulation for percent-of-total calculations in Power BI, and the correct mapping of SQL query result columns to chart axes in Azure Synapse Studio.

Implement and manage data models

Question

You need to create a DAX measure that will return the percent of total sales for each product. The measure must respect the report-level filter when calculating the total. How should you complete the measure? To answer, drag the appropriate DAX functions to the correct targets. Each function may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
You have an Azure Synapse Analytics workspace that is connected to an Azure Data Lake Storage Gen2 account. From Synapse Studio, you run the following query.
SELECT YEAR (OrderDate) AS OrderYear, SUM(UnitPrice * Quantity) + TaxAmount) AS GrossRevenue FROM dbo.orders GROUP BY YEAR(OrderDate) ORDER BY OrderYear;
You need to create a column chart to visualize the changes in gross revenue over time. How should you populate the chart? To answer, drag the appropriate columns to the correct settings. Each column may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Explanation

Approach. For the DAX measure, the correct pattern is DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLSELECTED())). ALLSELECTED() is critical here - unlike ALL() which strips every filter including slicers and report-level filters, ALLSELECTED() removes only the row/visual context filters while preserving any filters applied at the report level (e.g., page filters, slicer selections). CALCULATE() is required to override the current filter context so the denominator computes total sales across all products, not just the current row's product. DIVIDE() is used instead of the '/' operator to safely handle division-by-zero without errors. For the column chart in Synapse Studio, the SQL query produces two columns - OrderYear (the time dimension) and GrossRevenue (the aggregated metric). OrderYear maps to the X-axis (Axis/Category setting) because it represents the time progression, while GrossRevenue maps to the Y-axis (Values setting) because it is the quantity being measured over time - this correctly visualizes how gross revenue changes year over year.

Concept tested. DAX filter context and ALLSELECTED() vs ALL() for percent-of-total measures respecting report-level filters; mapping SQL result set columns (dimension vs. measure) to the correct chart axis settings in Azure Synapse Studio.

Reference. Microsoft Learn: DAX ALLSELECTED function - https://learn.microsoft.com/en-us/dax/allselected-function-dax; Azure Synapse Analytics - Visualize data with Synapse Studio charts

Topics

#DAX#Power BI Measures#Data Visualization#Chart Configuration

Community Discussion

No community discussion yet for this question.

Full DP-500 Practice