PL-300 Exam - Question 103

Question

DRAG DROP -You have a Power BI data model that contains two tables named Products and Sales. A one-to-many relationship exists between the tables. You have a report that contains a report-level filter for Products. You need to create a 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 DAX 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. NOTE: Each correct selection is worth one point.

Examice · Accepted Answer

.

GuerreiroJunior · Answer

Incorrect answer.

1.Calculate
2. ALLSELECTED.

ALLSELECTED Removes only the filter on the expression visual but respect all external filters.

romer0 · Answer

The tricky thing here is that the report contains a report-level filter for Products and you need to calculate all sales (for all products) for the divisor. So I'll share here both functions definition from dax.guide:

ALLSELECTED: Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside.

ALL: Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

So, for me the correct answer is CALCULATE & ALL

Kai_don · Answer

Answer is wrong. Second box should be allselected

Danylessoucis · Answer

Wrong. Correct answer is ALLSELECTED

RazaTheLegend · Answer

The tricky thing here is that the report contains a report-level filter for Products and you need to calculate all sales (for all products) for the divisor. So I'll share here both functions definition from dax.guide:

ALLSELECTED: Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside.

ALL: Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

So, for me the correct answer is CALCULATE & ALLSELECTED

SanaCanada · Answer

I was wrong in first comment

The correct answer is Calculate and Allselected
because

the ALL function removes all filters from the specified table or column, while the ALLSELECTED function retains the filters in the current filter context but removes filters from the specified table or column.

In Question Saying
respect all external filters

william_001 · Answer

the keyword here is "report level filter", ALL do not respect report level filter. ALLSELECTED respect the report level filter.

Pocu · Answer

Hi guys, please think twice before you vote or otherwise it will cause confusion to other users.
The key point is: The measure must respect the report-level filter when calculating the total.
ALL: Ignore all filters
ALLSELECTED: Respect all the filters outside of the current visual
If you still don't understand, try the below two measures when a slicer with all products existed:
Sales Pct based on all products= 
DIVIDE ( 
             SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ), 
             SUMX ( ALL ( Sales ), Sales[Quantity] * Sales[Net Price] ) 
)

No confusion, no need to discuss further.
Sales Pct based on selected products= DIVIDE ( 
SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ), 
SUMX ( ALLSELECTED ( Sales ), Sales[Quantity] * Sales[Net Price] ) )

SuniltheMentor · Answer

Definitely CALCULATE and ALLSELECTED
Using ALLSELECTED respects the filter defined on the page.

thomas_90 · Answer

In think that is so clear this this requirement "must respect report-level filter". So I will choose Calculate and ALLSELECTED.

ewelaela · Answer

CALCULATE and ALLSELECTED
Tested

Jew0598 · Answer

ALL() returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. Here, since it's mentioned that the measure must respect the report-level filter, the answer should be CALCULATE & ALLSELECTED as ALLSELECTED() retains all context filters.

SanaCanada · Answer

Correct Answer is Calcualte and All

The CALCULATE function is used to calculate the total sales amount for all products, but with the filter context of the report-level filter applied. We use the ALL function to remove the filter context of the Products table, so that the total sales amount is calculated for all products, regardless of the report-level filter.

No confusion to discuss further

UlyUkr · Answer

CALCULATE AND ALL, tested in Power BI. 
If ALLSELECTED is used, the moment you select a product, % of total sales calculation for that product becomes 100%, which is not correct.

vat4444 · Answer

Correct answer. CALCULATE & ALLSELECTED. Tested and works

Product Sale% = 
VAR ProductSales = SUM('Sales'[Sales])
VAR AllSales = CALCULATE(SUM('Sales'[Sales]),ALLSELECTED('Product'[Product Name]))
Return DIVIDE(ProductSales,AllSales)

Harish_98 · Answer

Tested!
Calculate
ALL

Dani_eL · Answer

Based on DAX definitions:
ALL :useful for CLEARING filters and creating calculations on all the rows in a table.
(https://learn.microsoft.com/en-us/dax/all-function-dax)

ALLSELECTED: gets the context that represents all rows and columns in the query, while KEEPING EXPLICIT FILTERS and contexts other than row and column filters. This function can be used to obtain visual totals in queries.
(https://learn.microsoft.com/en-us/dax/allselected-function-dax)

ALLSELECTED applies to one context(one visual)
ALL : if clearing means what it means, you will have no filters anymore

So for me : 
CALCULATE 
ALLSELECTED

JudT · Answer

- Calculate
- ALLSELECTED
Could be:
% of productcode sales = 
VAR productCode = SUM(Sales[Revenue])
VAR AllSales = CALCULATE(SUM(Sales[Revenue]), ALLSELECTED('Product'[Product Code]))
RETURN
DIVIDE( productCode , AllSales)

nelrosell · Answer

Must be ALLSELECTED as one of the requirements is "The measure must respect the report-level filter when calculating the total" ALL is wrong as it ignores any filters.

gilgir · Answer

With ALLSELECTED it doesn't work for me. Work with ALL !

Angelspace2023 · Answer

1.Calculate
2.AllSelected.

Filter requires the table name so it is an obviously wrong choice. ALL will not respect a report level filter for the field on which it is created and will be wrong based on this comment "The measure must respect the report-level filter when calculating the total".

niki_dat · Answer

ALLSELECTED

Mcisneros · Answer

ello govna

abbeyabh · Answer

1.Calculate
2. ALLSELECTED.

Chayma28 · Answer

Calculate
ALL

Tusker666 · Answer

ALL vs ALLSELECTED

ALL: its main function is to ignore the filters coming from other fields. 
ALLSELECTED: ALLSELECTED only ignores the filters coming from inside the visual itself, whereas ALL ignores any filters coming from anywhere in this visual or other visual on the page.

ApacheKafka · Answer

Answer is All.✔✔✔✔✔✔
Check this video https://youtu.be/1KfTmRYRLZs

agelee · Answer

The question is if they want to see always 100% per selected Products as Total. If yes then we need to use ALLSELECTED. If they want to see always the same '%of Total' for specific Product, regardless of selection (which is more logical from analytical point of view), then we should use ALL. If we analyze how much does one product contribute to Total Sales, the '%of Total' should always stay the same. But here instructions are misleading and maybe they want to see changing '% of Total' then ALLSELECTED is correct.

Male777 · Answer

Correct Answer is 
CALCULATE 
ALLSELECTED
.There is a narrow line of difference between ALL and ALLSELECTED and that is 
ALLSELECTED don't ignore Report level Filters which is the key requirement here if you read the question. 
Whereas ALL ignores any level of filter whether it is outside or inside.

Achu24 · Answer

Calculate ,ALL

SuniltheMentor · Answer

CALCULATE and ALLSELECTED. 
Use of all selected will show Sales % based on the selected Products in a Slicer. This is instead of showing sales % across all products

DataEngDP · Answer

ALLSELECTED is the correct answer,

Feact · Answer

All & Allselected are both fine. I'm currently studying for PL-300 and I've been going around for this one... I've just tested it and here's what I've learned.
If the function would have been: Calculate(Sales, ........ ('Products')) then ALLSELECTED is 100% the right choice. But since we are filtering only a column ('Products'[Product]) we can use either ALL or ALLSELECTED. Go ahead and test it on a small report, you'll se how it works. I really hope microsoft takes both answers as correct cause I've been thinking and testing for a while to realise about this.

jaume · Answer

My vote for CALCULATE and ALLSELECTED.
Tricky detail here (it looks like almost all questions have a tricky part) is "The measure must respect the report-level filter when calculating the total". Using ALL option would remove also that report-level filter so we need to use ALLSELECTED

Vadimasss1234 · Answer

This function is different from ALL() because it retains all filters explicitly set within the query, and it retains all context filters other than row and column filters.

Mohitsain · Answer

1.Calculate()
2. ALLSELECTED().

1sourabhpatel1 · Answer

The ALLSELECTED function is used to remove the report-level filter on the Products table, while still retaining any filters on other tables. This ensures that the calculation of total sales is only affected by the report-level filter applied to the Products table. The DIVIDE function is used to calculate the percentage of sales for each product based on the total sales.

luojihencha · Answer

ALL. ALLSELECTED keep visual filters. I don't why answers are often wrong and in comments people give wrong answer. Does it mean people doing power BI are stupid??? HAHA

Sabrus · Answer

I think it's CALCULATE and FILTER since it asks to respect the filter at the report level, the ALL function does not respect it. https://dax.guide/filter/

JoC9776100668 · Answer

Calculated and Allselected => Tested and ALL doesnt reflect page filter.

Ntaxiey_03 · Answer

1. CALCULATE
2.FILTER iterates through each row in the table

Igetmyrole · Answer

CALCULATE
ALL 
Percent of Product Sales =
VAR ProductSales = SUM('Sales'[sales])
VAR AllSales = CALCULATE(SUM('Sales'[sales]), ALL('Products'))
RETURN
DIVIDE(ProductSales, AllSales)

To explain ALL and ALLSELECTED 
ALL: Removes all filters from specified columns or tables.
ALLSELECTED: removes filters from specified columns or tables but respects visual or report-level filters.

WRTopics · Answer

I think is All.

momo1165 · Answer

because you need all sales, you will have to remove filters. the options are removefilters or All.
the correct answer is Calculate, All

10d9950 · Answer

Percent of Selected Product Sales =
VAR SelectedProductSales = SUM('Sales'[Sales])
VAR TotalSalesAllProducts = 
    CALCULATE(
        SUM('Sales'[Sales]),
        ALL('Products'[Product])
    )
RETURN
    DIVIDE(SelectedProductSales, TotalSalesAllProducts)

All will only work on product column while respecting the slicers from other columns, this is what is required in the question

JohnChung · Answer

AllSales measure is used as a denominator when calculating percentages, so i think it should include all sales data and the answer should be:

CALCULATE
ALL

Rhugved · Answer

Calculate and Allselected because the question is to respect the report level/external filter wile calculating TOTAL SALES. Allselected() helps us to keep visual selected or filter level context

HenryBiz · Answer

I prefer ALL over ALLSELECTED here because the name of the VAR is for all sales instead of all selected cells, I guess the intention is to have a denominator which includes all products and disregard the visual level filter, so ALL is more appropriate than ALLSELECTED here. By the way, both functions respect page and report-level filter.

alyes87 · Answer

Use ALL and not ALLSELECTED, as ALL does not respect filters at report level, whereas ALLSELECTED respects them with the exception of the selected column.

rcaliandro · Answer

For sure we have:
1 - CALCULATE
2 - ALL or ALLSELECTED but IDK which one :(

hebertorosillo · Answer

Incorrect answer.
1.Calculate
2. ALLSELECTED.

gpsaran · Answer

I wonder why so many answers are voted wrong. It should be CALCULATE, ALLSELECTED.

5bf040d · Answer

CALCULATE
ALLSELECTED

ALLSELECTED: Retains filters that are explicitly set within the query or report (e.g., slicers, filter visuals, report level filters, Dax filters). It removes only the row and column filters that are part of the current context.

ALL: Removes all filters, including those explicitly set in the query or report, and resets the context completely.

If you use ALLSELECTED, it will espect the report-level filter (only products data).

babi00 · Answer

ok one thing i just feel we need to use allselected cause it says report must respect report level filter , so we need to consider this percentage as for only the products which are slected in report , , guide me if my understanding is wrong

PL-300 Exam - Question 103

Discussion