Compare values of Same Dimension within Pivot Table after selection

[mrthomasshelby]

Hello,

I have a Pivot table as shown in the image:

As you can see, the data shown in the table is for a specific selected supplier, 'Supplier G'. The functionality that I need to have is to be able to compare the measures within the table with those ones of a 'identical group' or a 'competitor group' rather of suppliers which can be chosen from the list of suppliers available within the Supplier Field. For example, let's say Suppliers A,C,E are suppliers who are direct competitors of Supplier G. So, I want to be able to select the Suppliers A,C and E from the list of all available Suppliers and then show the measures('Spend Est.,000nd' and 'Quoted Cost by Supplier,000nd')  of these 3 suppliers beside the 2 measures of Supplier G(which is currently selected).

I have tried using the dropdown option for the dimension within Pivot Table but this removes my current selected 'Supplier G' and messes up the rank functions order used within my dimensions L1,L2 and L3. I am unable to think of another way this can be done. Please help me out in achieving this. I'm attaching the app as well. Thanks in advance!

stalwar1‌ kaushik.solanki‌ tresesco‌

[mrthomasshelby]

Hi Sunny! I have one last question. I'm trying to add in another measure named 'Delta to Cherry Pick' which calculates the difference between best quotes for that particular L1. For example, in the below image for L1 '4Serv116', the best(minimum) quoted price is that of Supplier E and the difference between the quote of Supplier B and Supplier E comes out to be 575, which is the calculated Delta to Cherry Pick measure.

Now the issue I'm facing is this measure is being calculated only for the Supplier being selected from the original Supplier field. The measure is not being calculated for Suppliers being selected from the 'SupplierUserSelection' field as you can see(all are coming out as 0s). How do I modify my expression so that this measure is calculated for all the 3 Suppliers?

To make it even more clear, for example, for the same L1 '4Serv116', for Supplier A this measure should be 1200(Supplier A Quote 2950-Least Quote(Supplier E)1750=1200) but it is 0 right now. Similarly for Supplier C it should be 1300 and for Supplier E it should be 0. Here's the expression I'm using for this measure:

sum({<Supplier = p(SupplierUserSelection)+p(Supplier)>}

aggr({<Supplier = p(SupplierUserSelection)+p(Supplier)>}([Quote Price]*([FX_Rate]/[FX Rate])*(1-Rebate)-min({<Supplier = p(SupplierUserSelection)+p(Supplier)>}TOTAL <[Baseline #]> [Quote Price]*([FX_Rate]/[FX Rate])*(1-Rebate)))*Volume, [Baseline #],Supplier)

)/1000

Attaching the file again in case you need it. Thanks a ton again!

[sunny_talwar]

Try this

sum({<Supplier = p(SupplierUserSelection)+p(Supplier)>}

aggr((Only({<Supplier = p(SupplierUserSelection)+p(Supplier)>}[Quote Price]*([FX_Rate]/[FX Rate])*(1-Rebate))-min({<Supplier = p(SupplierUserSelection)+p(Supplier)>}TOTAL <[Baseline #]> [Quote Price]*([FX_Rate]/[FX Rate])*(1-Rebate)))*

Only({<Supplier = p(SupplierUserSelection)+p(Supplier)>}Volume), [Baseline #],Supplier)

)

/1000

[sunny_talwar]

Yes you are right in your understanding

[mrthomasshelby]

I get it now. I overlooked including the set expression for the Volume part. Thanks a lot again Sunny!

[sunny_talwar]

Also, you had set analysis for the Aggr... whereas I added Only function within Aggr to add set analysis

[mrthomasshelby]

If you dont mind can you please explain how the Only function here makes the difference within the aggr part?

[sunny_talwar]

Running for work right now... will try to get back to this at a later time.

Best,

Sunny

[mrthomasshelby]

Thanks a ton again Sunny!