Allexcept

[Cyriackpazhe]

Can someone explain how the allexcept used as table expression in summarize work here. Is there any alternative to produce the required meaure

[bhanu_gautam · Accepted solution]

@Cyriackpazhe 

The ALLEXCEPT function in DAX is used to remove all filters from a table except for the specified columns. In the provided code, ALLEXCEPT is used within the SUMMARIZE function to create a summary table that includes all rows from the Winesales table, but only keeps the filter on the WINE column. This allows the calculation of the average yearly sales for each wine across all years.

Yearly Average Each Wine =
VAR Summarytable =
SUMMARIZE (ALLEXCEPT (Winesales, Wines [WINE]), DateTable [YEAR] )
RETURN
AVERAGEX (Summarytable, [Total Sales])

[Monika_Hr · Accepted solution]

If you have a Product table with five columns (ProductKey, ProductName, Brand, Flavor, Category), the following syntaxes produce the same result:

ALL ( Product[ProductName], Product[Brand], Product[Flavor] )

ALLEXCEPT ( Product, Product[ProductKey], Product[Category] )

My understanding base on your picture is, that our goal is to get result for average amount of wines sold and the average should be counted per year. I tried to use that approach with my data, where I use chocolate products instead of wines. I created one measure according your photo and another measure using ALL instead of the ALLEXCEPT. Then I controlled the results base on orders in Sales table. 

[bhanu_gautam · Accepted solution]

@Cyriackpazhe 

The ALLEXCEPT function in DAX is used to remove all filters from a table except for the specified columns. In the provided code, ALLEXCEPT is used within the SUMMARIZE function to create a summary table that includes all rows from the Winesales table, but only keeps the filter on the WINE column. This allows the calculation of the average yearly sales for each wine across all years.

Yearly Average Each Wine =
VAR Summarytable =
SUMMARIZE (ALLEXCEPT (Winesales, Wines [WINE]), DateTable [YEAR] )
RETURN
AVERAGEX (Summarytable, [Total Sales])

[Cyriackpazhe]

Is there any alterantive. Like using Values