ALLEXCEPT - how to respect the date column filter within a matrix visual

[Anonymous]

I have this measure - which when applied to a single date works fine. I want to be able to compare the percent of total for the date column. Currently when I add another date to the visual using the slicer, it recalculates the total as all dates included in the visual. 

1 Date (correct percentages)

2 dates: percent of whole is calculated with all dates instead of for the individual dates.

# Portfolio % = 
DIVIDE(
    [# Total_Market_Value_Base],
    CALCULATE(
        SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]),
        ALLSELECTED(FactPortfolioSectorDetails)
    )
)

I tried to use ALLEXCEPT instead and it just calculates 100% for every category.

# TEST Portfolio % = 
DIVIDE(
    [# Total_Market_Value_Base],
    CALCULATE(
        SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]),
        ALLEXCEPT(DimCalendar,DimCalendar[Date]),
        ALLEXCEPT(DimSector, DimSector[Sector], DimSector[Subcategory])
    )
)

I also tried having my ALLSELECTED filter, then adding the ALLEXCEPT on top of it and got the same result as my original measure.

# TEST Portfolio % = 
DIVIDE(
    [# Total_Market_Value_Base],
    CALCULATE(
        SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]),
        ALLSELECTED(FactPortfolioSectorDetails),
        ALLEXCEPT(DimCalendar,DimCalendar[Date])
    )
)

Thank you in advance.

[lbendlin · Accepted solution]

You may want to consider using REMOVEFILTERS instead.  

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

@talespin @lbendlin , I used REMOVEFILTERS instead and got my intended results, thanks very much.

[talespin]

hi @Anonymous 

You're welcome.

[talespin · Accepted solution]

hi @Anonymous ,

I am using AdventureWorks database. I have a Date Table and Fact Table linked on Order date.

[MSalesAmount] is nothing but SUM(SalesAmount).

Measure 

----------

Percent Of Total =

VAR _Total = CALCULATE([MSalesAmount], REMOVEFILTERS(DimProductCategory[EnglishProductCategoryName]) )

RETURN DIVIDE([MSalesAmount], _Total)

[lbendlin · Accepted solution]

You may want to consider using REMOVEFILTERS instead.  

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...