Calculate Costs based on Filter

[Yoshimitsu411]

Hello

I have three tables

1. Work Center
2. Network
3. Costs

Table layout and relationships

I want to create a slicer called ‘Work Group’ which is common to Work Centre and Network tables such that when I select a slicer value it will give me the sum of costs column from the Costs table for the related records. 

I am having trouble creating a common table called Work Group and then connecting it to Work Centre and Network tables. It will only allow one relationship and hence I am not able to filter the costs for all records from both tables.

Thanks for any suggestions.

Yoshi

[v-eqin-msft · Accepted solution]

Hi @Yoshimitsu411 ,

According to my understanding, you want to calculate the sum based on the matched value in Work Centre table or Network table,right?

Please follow these steps:

1. Add a new table and do not build relationships.

ForSlicer =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( 'Work Centre', "WorkGroup", [Work Group] ),
        SELECTCOLUMNS ( 'Network', "WorkGroup", [Work Group] )
    )
)

2. Create a sum measure

Measure =
VAR _all =
    ALLSELECTED ( 'ForSlicer'[WorkGroup] )
VAR _wono =
    SUMMARIZE (
        FILTER ( ALL ( 'Work Centre' ), 'Work Centre'[Work Group] IN _all ),
        'Work Centre'[Work Centre No]
    )
VAR _neno =
    SUMMARIZE (
        FILTER ( ALL ( 'Network' ), 'Network'[Work Group] IN _all ),
        'Network'[Network No]
    )
RETURN
    CALCULATE (
        SUM ( 'Costs'[Costs] ),
        FILTER (
            'Costs',
            'Costs'[Work Centre No]
                IN _wono
                || 'Costs'[Network No] IN _neno
        )
    )

The final output is shown below:

Here is the pbix file.

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

[Yoshimitsu411]

Hi @v-eqin-msft 

How can I use the Work Group slicer to filter the Costs table given there are two relationships to different tables?

Thank you.

Yoshi

[Yoshimitsu411]

Thanks @v-eqin-msft that was what I was after. Perfect!

Also thanks to @amitchandak for his contribution.

Yoshi

[amitchandak]

@Yoshimitsu411 , Assume three tables are joined as shown in figure

calculate(sum(Costs[cost]), filter( Network, network[Work Group] in values(Workcenter[work Group])))

[Work Group] should filter cost because of join and then Network will also filter for those work groups because of filter. It is like AND

[Yoshimitsu411]

Thanks @amitchandak for the quick response. I need to create a measure too as part of this solution so your suggestion is very much appreciated.

The report must use a slicer called Work Group.

My data and required output is as follows:

Note: Greyed columns do not exist in data table, I have added it for reference only.

 Thanks

[amitchandak]

@Yoshimitsu411 , can you share sample data of three table and output in table format.