Use Sum of value from a different table that could be filtered down

[ranz_vincent]

Hello can anyone help me I am trying to formulate (Manpower Cost/total_cbm)*(NCR Minimum wage/Minimum Wage of DC) that could be granulated per DC, per Month and per merch type, another problem is that total_cbm is in another table. Is this possible? Link for sample data - https://drive.google.com/drive/u/0/folders/1dyvn_6xC0yyh0AeVRY9atXw5y4v5zRBR

[v-menakakota · Accepted solution]

Hi @ranz_vincent ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

• To formulate (Manpower Cost/total_cbm)*(NCR Minimum wage/Minimum Wage of DC) .First create a relationship between Manpower[Merch_type] and OB Data[Merch_type].

Create measure:

ManpowerCostMetric =

VAR ManpowerCost =

    SUM('Manpower'[Manpower Cost])

VAR TotalCBM =

    CALCULATE(

        SUM('OB Data'[total_cbm]),

        TREATAS(VALUES('Manpower'[Merch_type]), 'OB Data'[Merch_type]),

        TREATAS(VALUES('Manpower'[DC]), 'OB Data'[DC]),

        TREATAS(VALUES('Manpower'[Month]), 'OB Data'[Month])

    )

VAR NCRWage =

    AVERAGE('Manpower'[NCR Minimum wage])

VAR DCWage =

    AVERAGE('Manpower'[Minimum Wage of DC])

RETURN

    IF(

        TotalCBM = 0 || DCWage = 0,

        BLANK(),

        (ManpowerCost / TotalCBM) * (NCRWage / DCWage)

    )

• This measure will give the aggregate values for DC,Month and Merch Type in visual or table.
• Create a Matrix Visual,use DC,Month and Merch Type as rows or cploumns.
• Add the measure ManpowerCostMetric as a value.
• Below is the screenshot of report for DC,Month and Merch Type with  ManpowerCostMetric.
  

  If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

  Thank you

[v-menakakota]

Hi @ranz_vincent ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

To be able to calculate of values from different table in power BI we need to ensure the relationship between the tables is properly configured, please let us know what column we should take as primary key between your two tables to further help you.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[danextian]

Hi @ranz_vincent 

Good job in providing a sample data. But you really ned to provide more context - which columns to pick and match. You're saying to make the result granulated but where is DC and Merch Type in the other table ? I'm seeing that what's in between parentheses could be the merch type but is that true for all rows? How about the location?

[ranz_vincent]

Sorry about the data, I added the merch type on the first table. What I want to get is the manpower cost column in the 2nd pic/total_cbm column in the 1st pic. I was hoping to show something like this 

[danextian]

Assuming that month is to be taken from wtdate.

Manpower cost calc column =
VAR _month =
    FORMAT ( 'OB Data 2 - Summary'[wtdate], "mmmm" )
RETURN
    CALCULATE (
        SUM ( 'MANPOWER COST (ALL)'[Manpower Cost] ),
        --sum of manpower cost based on the filters below
        FILTER (
            ALL ( 'MANPOWER COST (ALL)' ),
            'MANPOWER COST (ALL)'[Merch Type]
                = EARLIER ( 'OB Data 2 - Summary'[merch type] )
                && 'MANPOWER COST (ALL)'[DC2] = EARLIER ( 'OB Data 2 - Summary'[dcgroup] )
                && 'MANPOWER COST (ALL)'[Month] = _month
        )
    )

Note: merch type column in manpower cost table has preceding spaces so I trimmed them.

[ranz_vincent]

I tried using this but no values are shown. I change the 'OB Data 2 - Summary' to 'OB Sample' and 'Manpower Cost (ALL)' to 'Manpower Sample'. I used the formula on the 'OB Sample' table but nothing shows 

I also tried using it on 'Manpower Sample' table but there's an error

Maybe because I haven't related any columns from the table yet?

[danextian]

I did mention that merch type column in manpower cost table has preceding spaces so I trimmed them.

[ranz_vincent]

sorry about that I trimmed the wrong column and now the added column has values. Now I tried using the new column but everything I tried is way off from the values I wanted to get.

[v-menakakota · Accepted solution]

Hi @ranz_vincent ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

• To formulate (Manpower Cost/total_cbm)*(NCR Minimum wage/Minimum Wage of DC) .First create a relationship between Manpower[Merch_type] and OB Data[Merch_type].

Create measure:

ManpowerCostMetric =

VAR ManpowerCost =

    SUM('Manpower'[Manpower Cost])

VAR TotalCBM =

    CALCULATE(

        SUM('OB Data'[total_cbm]),

        TREATAS(VALUES('Manpower'[Merch_type]), 'OB Data'[Merch_type]),

        TREATAS(VALUES('Manpower'[DC]), 'OB Data'[DC]),

        TREATAS(VALUES('Manpower'[Month]), 'OB Data'[Month])

    )

VAR NCRWage =

    AVERAGE('Manpower'[NCR Minimum wage])

VAR DCWage =

    AVERAGE('Manpower'[Minimum Wage of DC])

RETURN

    IF(

        TotalCBM = 0 || DCWage = 0,

        BLANK(),

        (ManpowerCost / TotalCBM) * (NCRWage / DCWage)

    )

• This measure will give the aggregate values for DC,Month and Merch Type in visual or table.
• Create a Matrix Visual,use DC,Month and Merch Type as rows or cploumns.
• Add the measure ManpowerCostMetric as a value.
• Below is the screenshot of report for DC,Month and Merch Type with  ManpowerCostMetric.
  

  If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

  Thank you

[v-menakakota]

Hi @ranz_vincent ,

I just wanted to kindly follow up to see if you had a chance to review the previous response provided. Please let me know if it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.

Thank you.

[v-menakakota]

Hi @ranz_vincent ,

Hi 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-menakakota · Accepted solution]

Hi @ranz_vincent ,

I wanted to check if you had the opportunity to review the information provided. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[danextian]

Hi @ranz_vincent 

Please mark the post that has the solution as the solution, not the follow-up.

[ryan_mayu]

@ranz_vincent 

I  can't open the link. 

How to identify the totalcbm value in the first table? what columns that are linked between both table? could you pls elaborate more on that?

[ranz_vincent]

for the cbm its the total_cbm column and I am starting the model from scratch so there are no relationships yet. I updated the access for the files as well.