Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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 @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
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/
Anonymous
Not applicable

 

[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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.