Forum Discussion
Measure for sum when match between two tables
I have two table with related columns and perform action Table 1 (Spend): Category Supplier Spend Motor ABB 5000 Drive ABB 6000 Drive Siemens 8000 Cable ABB 9000 M...
Hi,
Here's my attempt:
- 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
- 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")
- In the visual, i dragged Category and Supplier from the preferred_supplier table
- I wrote the following measure in the Spend table
=CALCULATE(SUM(Spend[Spend]),Spend[Status]="Preferred")/SUM(Spend[Spend])
Here's the file.
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.
Hi,
In that case, please share a "well thought over dataset(s)" with your expected result.
