Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 # | Product | Revenue | Order-Concat |
123 | Phone | 100 | Phone-Tablet |
123 | Tablet | 200 | Phone-Tablet |
567 | Phone | 100 | Phone-Computer |
567 | Computer | 300 | Phone-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-Concat | Revenue |
Phone-Tablet | 100 |
Phone-Computer | 100 |
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-Concat | Revenue |
Phone-Tablet | 300 |
Phone-Computer | 400 |
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.
Solved! Go to 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.
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:
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.hi @Wkeith ,
Please check following steps as below:
1. Create a calculated table:
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:
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.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |