Forum Discussion

Yoshimitsu411's avatar
Yoshimitsu411
Icon for Resolver I rankResolver I
5 years ago
Solved

Calculate Costs based on Filter

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Yoshimitsu411
      Icon for Resolver I rankResolver I
      5 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Yoshimitsu411
      Icon for Resolver I rankResolver I
      5 years ago

      Hi Anonymous 

       

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

       

      Thank you.

       

      Yoshi