Allexcept

[Tmassey1243]

I am needing help with Allexcept or some other DAX that can solve my issue.

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. 

Any help with this would be great!

[v-zhangti]

Hi, @Tmassey1243 

Your screenshot is too small to see the data. Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tmassey1243]

This first Image Is showing all the items on the sales ticket, and the corresponding revenue. The Matrix also shows that (selected Revenue) is the sales price per item and the (All order Revenue) is the entire sales ticket revenue, summed up.  It may not appear so, but the Ticket Number Slicer on thr right actually has all the corresponding ticket numbers selected but unfortunately there is some odd 220K different tickets so it does not show the selection. - The Matrix Sums are doing what I need them to do but requires me to manually select the tickets in The Ticket Number Slicer.

The second image, is taking the product i selected in the (Product PN & Description) slicer which shows the matrix (same JDE Sales ticket number) and you will only see one item compared to the first images where it showed multiple sales items. You will notice that the (Selected Item Revenue) only shows the sales price of the one item (5,675) but the (All Order Revenue) is showing the total ticket Revenue which should match the first image above (18,253). You will also notice that on the matrix, it is summing all of the revenue. It says that all the tickets I have selected (slicer to the right) it sums the specific item number Revenue, it is also summing the total ticket revenue for all the selected Tickets (slicer on the right).

The last image brings me to my issue. This is a copy of Image two, but this time I have unselected the Tickets in the slicer to the right. You will notice that the Sum of (Selected Item Revenue) remains around the 246-449K (I have some anomalies with my data) but the Sum of (All Order Revenue) is now at 7.5MM. 

What seems to happen is that the measure I am using sums (All Order Revenue) correctly, only when I select the corresponding Ticket numbers in the slicer on the right but I need this to be automatic without using that slicer. 

I want to understand what is wrong with my measure, If i select all the tickets in the right slicer, it sums it correctly, but if i unselect them, it sums up revenue from all 215K tickets...

I can't provide a PBIX but.. if I can teams connect with you I could explain and shows the model from screan share.

This is the formula I am using. 

All Order Revenue = calculate(SUM(products[USD_EXT_PRICE]),ALLEXCEPT(products, products[ORD_NO],products[INVOICE_DATE]))

It sums correctly when using the Ticket Number Slicer but I don't want to use that because I would have to sort through 215K ticket numbers.. I want it to be automatic when I put in a Product PN & Description to make it all automatic.