Multiple sync'd slicers identifiable for use in measure

[nkiernan]

Hi,

I am working on a report to show vitality index of R&D machines in a manufacturing business. The report has four sync'd slicers on a large data set over a number of years.

These are Product Family, Product Line, Model, Manufacturing site.

The business has a number of Product families, and each of these contains a number of unique product lines. Each product line in turn has a number of unique machine models. Each of these models can be manufactured in at least one manfuacturing site, but some can be manufactured across a number of sites.

Example 1:

If I select a single product line in the Product Line slicer, this will automatically change the Product Line slicer to show the single relevant Product Line that that Product Family belongs to. The Model slicer shows all the machine models relating to that product line. All works well.

Example 2:

If I select a single machine model from the Model slicer, the Product Line slicer updates to show only the product line this model relates to, and simlarly the Product Family shows the single family the model relates to. Again, all works well.

Regardless of how a user uses the slicers (ability so single or multiple select from any of the four), I have been trying to create measures to calculte the sum of the revenue associated with each of the resulting selections in the four slicers. This would then get compared to overall business revenue. I've been trying a range of the calculate with filter options such as ALLSELECTED etc, but find that these only work when you filter Product Family, and then Product Line, and then Model.

So what I want to achieve, using Example 1 above as a baseline. User selects a single product line in that slicer. Product Family slicer updates to show only one product family, and so, calculate sum of the revenue for that complete product lfamily (as a result of selecting a product line slicer). Separetely, calculate sum of revenue for the complete product line that was selected originally. And also calculate the sum of the models remaining in the model slicer.

What I though I could do was create a measure to look at each slicer and work on the remaining/selected items. What seems to be happening is that measure only works on the slicer that was used.

Appreciate this isn't the most straightforward explanation.

[nkiernan]

Oh very nice @Anonymous , thank you very much for your reply and help. This is something I'd never have figured out!

I need to step through it and understand it more. It looks to work for single selections perfectly. If I select both Model 1 and Model 7 for example, this would relate to both Product Line A and C. Product Line revenue for 2023 for example shows 150 on the graph but should be 210. I'll study your measure and see if I can adjust it. Thanks again for your help

[Anonymous · Accepted solution]

Hi @nkiernan 

If you have multiple value, you can try the following.

03 Product Line Revenue =
VAR a =
    VALUES ( SalesData[Product Line] )
RETURN
    CALCULATE (
        SUM ( SalesData[Ex-Works Price (£)] ),
        ALL ( SalesData ),
        SalesData[Product Line]
            IN a
                && SalesData[Site]
                    IN VALUES ( SalesData[Site] )
                        && SalesData[Product Family]
                            IN VALUES ( SalesData[Product Family] )
                                && SalesData[Year] IN VALUES ( SalesData[Year] )
    )

Best Regards!

Yolo Zhu

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

[nkiernan]

Thank you, this looks to achieve exactly what I was after 😀

[Anonymous]

Hi @nkiernan 

Based on your descrpition, your measure will be affected by the slicers you have chosen, you can consider to use crossfilter() function to change the relationship direction among the tables, you can refer to the following link about the function.

CROSSFILTER function - DAX | Microsoft Learn

CROSSFILTER – DAX Guide

If the solution cannot meet your requirement, can you provide some sample data and the output you want so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

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

[nkiernan]

Thank you for your reply @Anonymous 

I created a scaled down example of what I am trying to achieve. I looked at the CROSSFILTER function but this might not be the solution.

I uploaded the pbix file to wetransfer storage so hopefully the link below works.
What I hope is shown is four slicers (Product Family, Product Line, Model, Manuf. Site). What I'm trying to achieve is in the Revenue Comparison graph, there should be three graph lines. 01 Total MS Revnue which shows the total revenue each year, and this is working. 04 Selected Model Revenue which shows the sum of revenue for only the resulting models after using the slicers. This seems to be working.

The final one, which I can't get working, is the sum of product line revenue but with a twist! Depending on what all slicer selections are made, I want to read the total sum of revenue for all the remaining/sliced product lines. Currently I can only get it to either be the total from the source table with no filters at all, or total that matches the selection of Model.

If for example I select Model 1 in the Model slicer, I want to see a line for revenue of Model 1 for each year, a second line for the total sum of revenue for Product Line - Line A (as Model 1 is only part of product line A), but product line A has other models also. I want all these included. The third line would show the total sum of revenue for all product families, lines, and models inclusive.

https://we.tl/t-KZA65p8GyE 

[Anonymous]

Hi @nkiernan 

You can try the following measure.

03 Product Line Revenue =
VAR a =
    MAX ( SalesData[Product Line] )
RETURN
    CALCULATE (
        SUM ( SalesData[Ex-Works Price (£)] ),
        ALL ( SalesData ),
        SalesData[Product Line] = a
            && SalesData[Site]
                IN VALUES ( SalesData[Site] )
                    && SalesData[Product Family]
                        IN VALUES ( SalesData[Product Family] )
                            && SalesData[Year] IN VALUES ( SalesData[Year] )
    )

Output

Best Regards!

Yolo Zhu

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