Forum Discussion

mahra-in's avatar
mahra-in
Helper II
8 years ago

Measure for sum when match between two tables

I have two table with related columns and perform action

 

Table 1 (Spend):

 

CategorySupplierSpend
MotorABB5000
DriveABB6000
DriveSiemens8000
CableABB9000
MotorGE1500
MotorSiemens3500
DriveSiemens3000
CableGE4500
DriveSiemens2500



Table 2 (Preferred Supplier):

CategorySupplier
MotorABB
CableGE
Drive

Siemens

 

The total spend from table 1 is 43000

 

I need a measure to check the combination of category and supplier from table 2 and sum the spend only when the combination exists in table 1

 

Motor ABB - 5000

Cable GE - 4500

Drive Siemens - 13500

Sum = 23000

 

I want the reuslt as 23000/43000 = 53%

 

Please help

 

 

 

 

 

 

 

 

 

 

 

 

 

Need Help

8 Replies

  • ccakjcrx's avatar
    ccakjcrx
    Resolver I
    8 years ago

    Hey mahra-in!

     

    Here is a screenshot of my solution:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Here is the measure I used:

     

    MsrSUMMatch = 
CALCULATE(
    SUM(Spend[Spend]),
        FILTER(Spend,
            Spend[Category] = RELATED(PreferredSupplier[Category])
            && Spend[Supplier] = RELATED(PreferredSupplier[Supplier])
        )
)

     

    The percentage measure is as follows:

     

    MsrSUM% = 
DIVIDE([MsrSUMMatch],SUM(Spend[Spend]))

    Click HERE to access my .pbix file. 

     

    I hope this helps! Have a good weekend.

    • mahra-in's avatar
      mahra-in
      Helper II
      8 years ago

      Hi

       

      Sorry I missed to mention the Table 1 & Table 2 are not directly related since both tables has category repeated and hence I had created a bridge table to relate these tables

       

      Now when I apply the measure you sent me the related is not working

       

      Can you help me on that.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Here's my attempt:

     

    1. I created a relationship between the Category column of the Spend Table to the Category column of the preferred_supplier table.  I have assumed that in the Category column of the preferred_supplier table, there will be no duplicates
    2. In the spend Table, i wrote the following calculated column formula

    =if(not(ISBLANK(LOOKUPVALUE(preferred_supplier[Category],preferred_supplier[Category],Spend[Category],preferred_supplier[Supplier],Spend[Supplier]))),"Preferred","General")

     

    1. In the visual, i dragged Category and Supplier from the preferred_supplier table
    2. I wrote the following measure in the Spend table

    =CALCULATE(SUM(Spend[Spend]),Spend[Status]="Preferred")/SUM(Spend[Spend])

     

    Here's the file.

     

     

    • mahra-in's avatar
      mahra-in
      Helper II
      8 years ago

      Hi Ashish

       

      The measure is not working since I made the mistake by giving incomplete info. The tables tables were not directly related and both tables have repeated category for eg: Motor is preferred for ABB , Siemens and so on. Hence I created a bridge table to related these table.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        In that case, please share a "well thought over dataset(s)" with your expected result.

  • BeidouZh's avatar
    BeidouZh
    New Member
    8 years ago

    HI 

    My resolution:

    1. add a customer column in sheet 2, named as your request, such as " perfect choice"... and filled with “yes”

    2. merge sheet2 to sheet 1 when category and supplier are both matched in the two sheet. and get the "yes" column

    3. add new quick measure-filted value:  calculate the speed column,by filte "yes" column. named "yes value".

    4. add new quick measure-division:  get  (SUM Speed)/ (SUM yes value). it is 0.53.

    sorry,I don't have resource to update the my screen picture. wish it helpful.

  • BeidouZh's avatar
    BeidouZh
    New Member
    8 years ago

    HI 

    My resolution:

    1. add a customer column in sheet 2, named as your request, such as " perfect choice"... and filled with “yes”

    2. merge sheet2 to sheet 1 when category and supplier are both matched in the two sheet. and get the "yes" column

    3. add new quick measure-filted value:  calculate the speed column,by filte "yes" column. named "yes value".

    4. add new quick measure-division:  get  (SUM Speed)/ (SUM yes value). it is 0.53.

    sorry,I don't have resource to update the my screen picture. wish it helpful.