Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
3 years ago
Solved

ALLEXCEPT for multiple columns/Filters

Hi,

I need help with the following DAX formula:

VAR V1 = CALCULATE([Revenue],ALLEXCEPT(Revenue,Revenue[Contract]))
RETURN
DIVIDE([Revenue],V1)

 

What I Need is to use different filters to calculate the % of total only for the information filtered so I can apply to other measures.

 

The problem is when I use filters that is not "Contract" is calculating based on all that is not filtered. 

Meaning/Example: In this case When I filter by FY22 it gives 23,09 %  but I need 100% like above.

 

Hope you can help me .

 

Thanks !

 

 

 

 

Data Protection
General Comment
Need Help
  • Mahesh0016's avatar
    Mahesh0016
    3 years ago
    Hello cristianml ,
    % listPrice =
    VAR V1 = CALCULATE([Total listPrice],ALLSELECTED(TrainingSample2[Business Segment]))

    RETURN

    DIVIDE([Total listPrice],V1,0)

     

    Please try this one.

4 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    cristianml,

     

    In your measure [_01 % Revenue], have you tried using V3 in line 6 instead of V1?

     

    DIVIDE ( [Revenue], V3 )

     

    This will cause the denominator (V3) to use the filter context that exists outside the visual (i.e. slicers and filters). If this doesn't solve the issue, please provide a link to a sample pbix using one of the file services like OneDrive.

  • Vilmar's avatar
    Vilmar
    Resolver I
    3 years ago

    Tente:

     

    _01 % Revenue =
DIVIDE (
    [Revenue],
    CALCULATE ( [Revenue], REMOVEFILTERS ( [Revenue[Contract] ) )
)
  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago
    Hello cristianml ,
    % listPrice =
    VAR V1 = CALCULATE([Total listPrice],ALLSELECTED(TrainingSample2[Business Segment]))

    RETURN

    DIVIDE([Total listPrice],V1,0)

     

    Please try this one.