Alternative to allexcept / avoiding using calculate modifiers

[Applicable88]

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. I also try to limit the use of calculate. 

A very common expression I use is:

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

Can anyone show me a few alternatives to that or an  X-aggregated version or maybe with table variables inside?

Thank you very much in advance. 

Best. 

[Applicable88]

@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 · Accepted solution]

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.

[amitchandak]

@Applicable88 , Not ver clear. But this will not follow the filters. To work with those try like

Measure = Calculate ( SUM ( 'Salestable' [Sales], Filter(allselected( 'Salestable') ,'Salestable', [Categories] = max('Salestable', [Categories]) ) )

Measure = Calculate ( SUM ( 'Salestable' [Sales], Filter(all( 'Salestable') ,'Salestable', [Categories] = max('Salestable', [Categories]) ) )

[Tmassey1243]

@amitchandak 

- I have a challenge and I have been able to partially solve it with the measure Allexcept. I have read your reply and tried to apply a filter into the formula but I keep getting errors and I am not entirly sure I am following the instructions correctly. 

So, I have a product (PN:2848527 green line) that is sold on a sales ticket that I can capture revenue on. For this example, it is $115K (blue line) and it comes through on my matrix/tiles clear. On those sames sales tickets, I have other sales items (pull-through revenue) that I want to sum based only on the sales tickets that have the PN:2848527 on. 

The goal is to show the pull through revenue which should be $356K (blue line). I can do this manually, as shown in the image by manually selecting all of the sales ticket numbers (red line) but I want it to be automatic to a matrix or a tile.

Currently, in my attempt to use a measure  All Order Revenue = calculate(sum(products[USD_EXT_PRICE]), ALLEXCEPT(products, products[ORD_NO],products[INVOICE_DATE]))

USD_EXT_PRICE- Revenue

ORD_NO- Ticket Number (Sales Ticket)

This screen shot shows what the formula is doing thus far. I can get it to show automatically without selecting my Ticket numbers (red line) but my challenge is that my "All Order Revenue" is not totalling $356K like it should but it rather totalling ALL line items I have at $7.42 Bn. and not isolating to the specific ticket numbers shown in the brown box. 

Can this be done with a measure, or will I have to go into the databases with some sort of data table? I am not a code person so I may need some in depth explanation.