Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wkeith
Helper II
Helper II

Filter to only see orders that contain a certain product but still able to see full order value

Hi,

 

My data is structured in a way that each line in my fact sales table is at the item level of a larger order. For example:

 

Order #ProductRevenueOrder-Concat

123

Phone100Phone-Tablet
123Tablet200Phone-Tablet
567Phone100Phone-Computer
567Computer300Phone-Computer

 

Order-Concat is just a simple calculated column that allows us to see which items are being bought together in the same order.

 

I am currently using the Product column as a slicer on my report so that I only see orders that contain certain products, lets say Phones. But when I filter for Phones it gets rid of the other parts of order and only shows the phones revenue in my desired table. Below is what currently is happening:

 

Order-ConcatRevenue
Phone-Tablet100
Phone-Computer100

 

This is not what I want to be happening because my table incorrectly states that the order values are only 100 because it filters out the lines that contain the other portions in the orders, like Computers and Tablets. Expected result:

 

Order-ConcatRevenue
Phone-Tablet300
Phone-Computer400

 

 

I would just use the Order-Concat column as a slicer but there are hundreds of combinations in this column and to click through each one would not be ideal/work for the end user. That is why the slicer must be the Product column in my case.

 

I know this is complicated but basically I want to only show orders that contain a certain product but I want to be able to see the those full order values and not just the individual product that is selected value. 

 

 

Thanks in advance and I will be around to answer any questions. 

 

@Cmcmahan 

1 ACCEPTED SOLUTION


@Wkeith wrote:

How would I modify this measure so that it also adjusts when I filter by other fields as well? (I.E. I might have a customer slicer or a sales manager slicer also on my report page). Also I have Page Level filters on my page, does this measure ignore those page/report level filters? If so, how can I modify it so that it takes these filters into account? 


So the last measure I posted should take other filters into consideration automatically (provided those slicers are connected to the table with the [Order #] column in such a way that the affect the base Revenue amount)

 

Order Total = CALCULATE( SUM('Table'[Revenue]), All('Table'), VALUES('Table'[Order #]))

 

This is because the VALUES('Table'[Order #]) expression effectively returns a list of all the distinct order numbers in the context of the current set of slicers. So if you have a slicer on date and another on product, this expression will only return order #'s which match the selected date and product.

View solution in original post

9 REPLIES 9
v-jayw-msft
Community Support
Community Support

hi @Wkeith ,

 

Sorry for the mistake I have made above.

Actually, there's no need to create calculated table, here's modified measure for you:

 

Measure =
var sv = SELECTEDVALUE('sales'[Product],BLANK())
var ord = CALCULATE(MAX(sales[Order #]),FILTER(sales,sales[Product] = sv))
return
CALCULATE(SUM(sales[Revenue]),FILTER(ALL(sales),sales[Order #] = ord))
 

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
v-jayw-msft
Community Support
Community Support

hi @Wkeith ,

 

Please check following steps as below:

1. Create a calculated table:

1.PNG

2. Create measure:

    Measure =

    var sv = SELECTEDVALUE('Table'[Product],BLANK())

    var ord = CALCULATE(MAX(sales[Order #]),FILTER(sales,sales[Product] = sv))

    return

    CALCULATE(SUM(sales[Revenue]),FILTER(sales,sales[Order #] = ord))

3. Results would be shown as below:

2.PNG3.PNG4.PNG

Hopefully works for you, BTW, PBIX as attached.

 

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
d_gosbell
Super User
Super User

So in theory you should be able to do something like the following:

 

Order Total = CALCULATE(SUM('Table'[Revenue]), ALLEXCEPT('Table', 'Table'[Order #]))

 

But that will only work when you have the order # included in your visual.

 

If that is not always going to be the case you could use the following pattern:

Order Total = CALCULATE( SUM('Table'[Revenue]), All('Table'[Product]), ALL('Table'[Revenue]))

 

 

@d_gosbell . I do not want to have order # in my table because then my table will be thousands of records long. I just want the Concatenated version of orders (I.E. Phone-Tablet, Computer-Tablet, etc.) and the total revenue amount for each bucket.

 

I will try your second solution in this case and get back to you if it works. 


@Wkeith wrote:

@d_gosbell . I do not want to have order # in my table because then my table will be thousands of records long. I just want the Concatenated version of orders (I.E. Phone-Tablet, Computer-Tablet, etc.) and the total revenue amount for each bucket.

 


OK, so the second measure will do that.

 

Another approach which may be more robust is the following which strips off all the filter context from the table, then just adds back the order numbers in the current filter context

 

Order Total = CALCULATE( SUM('Table'[Revenue]), All('Table'), VALUES('Table'[Order #]))

 

@d_gosbell. Thanks for the reply. It appears this measure is close to achieving the expected result but I need to modify it slightly and need help doing so. How would I modify this measure so that it also adjusts when I filter by other fields as well? (I.E. I might have a customer slicer or a sales manager slicer also on my report page). Also I have Page Level filters on my page, does this measure ignore those page/report level filters? If so, how can I modify it so that it takes these filters into account? 


@Wkeith wrote:

How would I modify this measure so that it also adjusts when I filter by other fields as well? (I.E. I might have a customer slicer or a sales manager slicer also on my report page). Also I have Page Level filters on my page, does this measure ignore those page/report level filters? If so, how can I modify it so that it takes these filters into account? 


So the last measure I posted should take other filters into consideration automatically (provided those slicers are connected to the table with the [Order #] column in such a way that the affect the base Revenue amount)

 

Order Total = CALCULATE( SUM('Table'[Revenue]), All('Table'), VALUES('Table'[Order #]))

 

This is because the VALUES('Table'[Order #]) expression effectively returns a list of all the distinct order numbers in the context of the current set of slicers. So if you have a slicer on date and another on product, this expression will only return order #'s which match the selected date and product.

itsmebvk
Continued Contributor
Continued Contributor

@Wkeith  Please watch attached video for steps. Please correct me if I misunderstood your requirement and provide me more sample data.

 

@itsmebvk . You are missing the point. Look at Order #591 for example this is an order that has a phone sold in it but the way your filter is working currently that order is not pulled through into the table. I want to pull all orders where a phone is sold no matter the ordering of where "phone" lands in the concatenated column. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.