Please help with writing a set expression for the requirement below!
I have three tables i am importing as shown below, Table 1, Table 2 and Table 3. What i want to do is basically make KPI boxes with "Low", "Medium", "High" as shown in the guideline to show revenue in each of the category as shown in the Result below.
To explain Table 1 has Main Part, Components and Supplier for each component. Table 2 has revenue in Main part level i.e Revenue will be for A not for A1,A2 or A3 components. and Table 3 will have scores for suppliers in the range of 1 to 100.
So what the expression should do is for A since the suppliers are S1,S2 and S3 but since S3 has the highest score i.e 65 the revenue for A i.e $2000 will be associated with "High" and similarly B,C,D will be associated with "Medium" since there is only one supplier and and these suppliers fall under medium so the "Medium" KPI should sum to $5500.
Can you help me in writing a chart expression for this? Basically three KPI boxes for Low, Medium and High showing total revenue in that category.
40 to 60
Low: $0 Medium : $5500 ($1000 + $1500 +$2000) High:$2000