Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I need your help for the below
I have two tables with repeated values as below
Table 1: "Spend" (Excel Data)
Supplier | Category | Spend |
Weg | Motor | 5000 |
ABB | Drive | 4000 |
Siemens | Cables | 1000 |
TECO | Motor | 5500 |
Siemens | Cables | 1500 |
TMEIC | Cables | 1200 |
ABB | Motor | 4500 |
Weg | Drive | 3500 |
Siemens | Drive | 3200 |
GE | Drive | 3000 |
ABB | Motor | 4000 |
GE | Cables | 1100 |
TMEIC | Motor | 6000 |
|
| 43500 |
Table 2: "Preferred Supplier" (Sharepoint List - online)
Supplier | Category |
Weg | Motor |
Weg | Drive |
ABB | Motor |
Siemens | Motor |
Siemens | Cables |
GE | Drive |
TMEIC | Cables |
Since the above two tables have repeated values in both Supplier & Category I creatd a Bridge Table as below
Table 3: Category Bridge
Category |
Motor |
Drive |
Cables |
And manage relationship with Table 1 & Table 2
Now what I need is When there is a match of Supplier & Catgeory from Table 2 with Table 1, add that Spend and finally divide the total with grand total
For Eg: Weg preferred for Motor & Drive and the related spend is 5000 & 3500 respectively
Similaly ABB for Motor alone and the related spend in Table 1 is 4500 + 4000
Overall the sum of spend when there is a match is 23700 and the total spend is 43500
Now 23700/43500 = 54% is the reslut I need
Regards
Mahes r
Solved! Go to Solution.
This solution only requires Tables 1 and 2. No bridge table, no relationships.
Add this calculated column to Table 1 - Spend:
Preferred = VAR Test = COUNTROWS ( FILTER ( PreferredSupplier, PreferredSupplier[Supplier] = Spend[Supplier] && PreferredSupplier[Category] = Spend[Category] ) ) RETURN IF ( Test = 1, "Yes", "No" )
The Test variable checks to see if there is a match against the Preferred Supplier table, matching both on Supplier and Category. If there is, 1 row will be returned. Then the result in the column will be "Yes".
Then you can create the following measures:
[Total Spend] = SUM ( Spend[Spend] )
[Approved Spend] = CALCULATE ( [Total Spend], Spend[Preferred] = "Yes" )
[% Approved Spend] = DIVIDE ( [Approved Spend], [Total Spend] )
Hi ChrisHaas
Your solution is working fine with small correction but I dont know why
Preferred =
VAR Test =
COUNTROWS (
FILTER (
PreferredSupplier,
PreferredSupplier[Supplier] = Spend[Supplier]
&& PreferredSupplier[Category] = Spend[Category]
)
)
RETURN
IF ( Test = 1, "Yes", "No" )
In the above calculated column I chnaged the If statement as
IF ( Test = 0, "No", "Yes" )
remining everything I keep as you mentioned and working perfect.
But I said I still dont know what and why it gave wrong value first and right value once I changed the if statement
Thanks for the solution, it works now
This solution only requires Tables 1 and 2. No bridge table, no relationships.
Add this calculated column to Table 1 - Spend:
Preferred = VAR Test = COUNTROWS ( FILTER ( PreferredSupplier, PreferredSupplier[Supplier] = Spend[Supplier] && PreferredSupplier[Category] = Spend[Category] ) ) RETURN IF ( Test = 1, "Yes", "No" )
The Test variable checks to see if there is a match against the Preferred Supplier table, matching both on Supplier and Category. If there is, 1 row will be returned. Then the result in the column will be "Yes".
Then you can create the following measures:
[Total Spend] = SUM ( Spend[Spend] )
[Approved Spend] = CALCULATE ( [Total Spend], Spend[Preferred] = "Yes" )
[% Approved Spend] = DIVIDE ( [Approved Spend], [Total Spend] )
In this step
RETURN IF ( Test = 1, "Yes", "No" )
what if I want to return value from table preferred supplier, what can I do?
Hi ChrisHaas
Your solution is working fine with small correction but I dont know why
Preferred =
VAR Test =
COUNTROWS (
FILTER (
PreferredSupplier,
PreferredSupplier[Supplier] = Spend[Supplier]
&& PreferredSupplier[Category] = Spend[Category]
)
)
RETURN
IF ( Test = 1, "Yes", "No" )
In the above calculated column I chnaged the If statement as
IF ( Test = 0, "No", "Yes" )
remining everything I keep as you mentioned and working perfect.
But I said I still dont know what and why it gave wrong value first and right value once I changed the if statement
Thanks for the solution, it works now
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |