All Except is not behaving correctly on PBI

[Zaytoon]

just noticed wrong calcuation by PBI after latest SW version, here is dummy table and DAX expression i tried on excel and it worked perfectly fine and tried on PBI but not giving the correct results.
the measure i created on excel & PBI is :measure 1:=DIVIDE(CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Category],Table1[Month])),CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Month])))

the table 

Month	Category	Sales
Jan-23	Books	2
Jan-23	Pens	3
Jan-23	Cards	3
Feb-23	Books	2
Feb-23	Cards	1
Mar-23	Books	1
Mar-23	Cards	2

i created 2 slicers (month & category) where i expected sales values for selected category in the selected months to be divided by 
sales values of all categories in the selected months, however it only works when im clearing the months selection despite the fact that its working 100% accurate in excel.

[Adamboer]

It looks like there might be an issue with the context being used in your DAX expression. The ALLEXCEPT function is removing filters from all columns except for the specified ones, which may not be what you want in this case.

Instead, you could try using the FILTER function to explicitly filter the table based on the selected month and category, and then calculate the numerator and denominator separately. Here is an example of what the DAX expression could look like:

measure 1 := VAR selected_month = SELECTEDVALUE(Table1[Month]) VAR selected_category = SELECTEDVALUE(Table1[Category]) VAR numerator = CALCULATE( SUM(Table1[Sales]), Table1[Month] = selected_month, LEFT(Table1[Category], 2) = IF(selected_category = "All", LEFT(Table1[Category], 2), LEFT(selected_category, 2)) ) VAR denominator = CALCULATE( SUM(Table1[Sales]), Table1[Month] = selected_month, ALL(Table1[Category]) ) RETURN DIVIDE(numerator, denominator)

This expression uses the LEFT function to extract the first two characters of the Category column, which should match the prefixes used in your original logic. It also checks if the selected category is "All", in which case it includes all categories that have the same prefix as the selected month.

Let me know if this helps or if you have any further questions!

[tamerj1]

Hi @Zaytoon 

Indeed Power Pivot and Power Bi DAX engines are different and they somethimes behave differently. With Power Bi measure it is recommended to use

1 =
DIVIDE (
    CALCULATE (
        SUM ( Table1[Sales] ),
        ALL ( Table1 ),
        VALUES ( Table1[Category] ),
        VALUES ( Table1[Month] )
    ),
    CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1 ), VALUES ( Table1[Month] ) )
)

[andhiii079845]

Yes than you have the probem. You will filter the table with a slicer before the other calculations, ALL() will not help you in this case. Please create a separate table with DISTINCT values of the columns for the slicer and use this column in your table. You can create a relation between both tables.

[andhiii079845]

Do you create separate tables for the slicer columns or do you use the columns from Table1? It seems that you have this problem:
https://www.sqlbi.com/articles/understanding-dax-auto-exist/

[Zaytoon]

Thanks andhiii079845 actually slicers are from the same table