cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PatrykRoz
New Member

Calculating other values within same visual table based on given criteria

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.

 

PatrykRoz_0-1626476906568.png

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

 

3 REPLIES 3
PatrykRoz
New Member

Hey @daxer , @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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
daxer
Solution Sage
Solution Sage

 

[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
)

 

 

 

 

 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.