Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Motor | GE | 1500 |
Motor | Siemens | 3500 |
Drive | Siemens | 3000 |
Cable | GE | 4500 |
Drive | Siemens | 2500 |
Table 2 (Preferred Supplier):
Category | Supplier |
Motor | ABB |
Cable | GE |
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
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.
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.
Hi,
Here's my attempt:
=if(not(ISBLANK(LOOKUPVALUE(preferred_supplier[Category],preferred_supplier[Category],Spend[Category],preferred_supplier[Supplier],Spend[Supplier]))),"Preferred","General")
=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.
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.
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.
Hi @mahra-in,
I do not know whom you are replying to but did you try my solution?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
99 | |
38 | |
37 |
User | Count |
---|---|
158 | |
125 | |
76 | |
74 | |
63 |