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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataUsurper
Helper II
Helper II

Calculating The Avg Price from Distinct Set of Prices

Hello PBI Guru's,

 

I am attempting to calculate the average price of an item where the $ > 0 and averaged only by the distinct price and not the number of lines (records).

Image 1: Data structure

PBI2.png

 

Image 2:

1. List of distinct gear charges

2. Sum of distinct gear charges

3. Correct, expected average of distinct prices.

PBI1.png

 

I've searched and founda few similar posts but was not able to replicate that correct answer. Any help is greatly appreciated.

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Calc :=
AVERAGEX (
    SUMMARIZE ( 'Table', 'Table'[ProductName], 'Table'[Price] ),
    'Table'[Price]
)

Try this, just remplace 'Table' with your table name.  This will create the unique list of products and prices, then using averagex, iterate over these to create the average price.

View solution in original post

Anonymous
Not applicable

CALCULATE
(
	AVERAGEX
	(
		SUMMARIZE
		(
			'Table'
			,'Table'[ProductName]
			,'Table'[Price]
		)
		,'Table'[Price]
	)
	,FILTER
	(
		VALUES('Table'[Price])
		,NOT(ISBLANK('Table'[Price])) && 'Table'[Price] <> 0 
	)
)

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Calc :=
AVERAGEX (
    SUMMARIZE ( 'Table', 'Table'[ProductName], 'Table'[Price] ),
    'Table'[Price]
)

Try this, just remplace 'Table' with your table name.  This will create the unique list of products and prices, then using averagex, iterate over these to create the average price.

Thank you, that's the format. I was trying it with the Averagex/Summarize swapped. Is there a place where I can only include > 0 records? No blanks or 0s

Anonymous
Not applicable

CALCULATE
(
	AVERAGEX
	(
		SUMMARIZE
		(
			'Table'
			,'Table'[ProductName]
			,'Table'[Price]
		)
		,'Table'[Price]
	)
	,FILTER
	(
		VALUES('Table'[Price])
		,NOT(ISBLANK('Table'[Price])) && 'Table'[Price] <> 0 
	)
)
vanessafvg
Super User
Super User

@DataUsurper  any chance you can share the data in text not image?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.