Forum Discussion
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. 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.
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.
4 Replies
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]) ) )
- 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.
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?
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.
