Forum Discussion

Applicable88's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
4 years ago
Solved

Alternative to allexcept / avoiding using calculate modifiers

Hello,   Allexcept as an Calculate modifier, can be used for example to sum sales, but leavin one filter like "Category".    But in my own experience calculate functions can run into limitations....
Need Help
Tips and Tricks
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Applicable88 

     

    You can try measures like 

    Measure 1 =
SUMX (
    FILTER (
        ALL ( 'Salestable' ),
        'Salestable'[Categories] IN VALUES ( 'Salestable'[Categories] )
    ),
    'Salestable'[Sales]
)

    Or 

    Measure 2 =
SUMX (
    FILTER (
        ALL ( 'Salestable' ),
        'Salestable'[Categories] = SELECTEDVALUE ( 'Salestable'[Categories] )
    ),
    'Salestable'[Sales]
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

Applicable88's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
4 years ago

amitchandak , I wanted to know if there are alternative syntax without Calculate and Allexcept?

 

Can I  solve 

Measure = Calculate ( SUM ( 'Salestable' [Sales], Allexcept ( 'Salestable', [Categories])) 

with another formula? Either a measure with an table variable or with an Measure with an x-aggregated function?

 

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Applicable88 

     

    You can try measures like 

    Measure 1 =
SUMX (
    FILTER (
        ALL ( 'Salestable' ),
        'Salestable'[Categories] IN VALUES ( 'Salestable'[Categories] )
    ),
    'Salestable'[Sales]
)

    Or 

    Measure 2 =
SUMX (
    FILTER (
        ALL ( 'Salestable' ),
        'Salestable'[Categories] = SELECTEDVALUE ( 'Salestable'[Categories] )
    ),
    'Salestable'[Sales]
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.