Calculating other values within same visual table based on given criteria

[PatrykRoz]

Dear All,

I am looking for DAX Measure which could help me with below issue.

My visual table contains information about fabric id, product code, expiration date for that product, stock quantity and sales value.

For each key (product code & expiration date of that product) i have to verify if other fabrics are selling better same products, if yes i would need to receive a suggestion of such fabric ID within additional column in my visual table.

Example:
Product code: 5701943011140, Expiration date: 31.08.2021 is sold by fabrics: 858479 and 894970. Fabric 858479 is seeling better that product than fabric 894970 (Sales value 0,72 in comparison to 0,23), therefore I need to put information in the line with fabric 894970 within additional column: "Suggestion" fabric id which sells that product better.

Could You please advise how to write required DAX measure or what should I do to receive described results ?

Thank You in advance for your help !

Regards,
Patryk

[PatrykRoz]

Hey @Anonymous , @Ashish_Mathur ,

Thanks for quick replies. I just checked and code provided by daxer is almost working perfectly ! Tomorrow I will share full annonimized dataset, so you could support me on more realistic data.

[Ashish_Mathur]

Hi,

What does "selling better" mean when there are more than 2 product code and expiration combinantion rows? So for Product code: 5701943011140 and Expiration date: 31.08.2021, if there were 10 rows then for 9 rows would you want to see the only that one Fabric code which has the highest sale.  Please take a realistic/representative sample dataset and show the result on that dataset.

[Anonymous]

[Suggestion] = // measure
IF ( ISINSCOPE( T[Fabric_Code] ),
	var CurrentFabric = SELECTEDVALUE( T[Fabric_Code] )
	var CurrentSalesValue = [Sales Value]
	var CurrentKey =
		SUMMARIZE(
			T,
			T[EAN_Code],
			T[Expiration_Date]
		)
	var CurrentKeyIsSingle =
		COUNTROWS( CurrentKey ) = 1
	var Result =
		IF( CurrentKeyIsSingle,
			var BetterFabric = 
				MAXX(
					CALCULATETABLE(
						TOPN(1,
							FILTER(
								DISTINCT( T[Fabric_Code] ),
								[Sales Value] > CurrentSalesValue
							),
							[Sales Value],
							DESC
						),
						ALLSELECTED( T[Fabric_Code] )
					),
					T[Fabric_Code]
				)
			return
				BetterFabric
		)
	return
		Result
)