Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

All Except is not behaving correctly on PBI

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 



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.

Responsive Resident
Responsive Resident

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!

Super User
Super User

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 =
        SUM ( Table1[Sales] ),
        ALL ( Table1 ),
        VALUES ( Table1[Category] ),
        VALUES ( Table1[Month] )
    CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1 ), VALUES ( Table1[Month] ) )
Super User
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User
Super User

Do you create separate tables for the slicer columns or do you use the columns from Table1? It seems that you have this problem:

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Thanks andhiii079845 actually slicers are from the same table

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.