DAX sums averages where it theoretically shouldn't

[Anonymous]

Hello Power BI Guru's!

I have a problem where dax sums up the averages for each position when it shouldn't.

I have a calculated measure for Price per kg weighted by Quantity per Position sorted by Month and Position

Price per kg weighted by Quantity per Position = 
VAR __CATEGORY_VALUES = VALUES('SALES'[Position])
RETURN
	SUMX(SALES, DIVIDE(
		SUMX(
			KEEPFILTERS(__CATEGORY_VALUES),
			CALCULATE([Price per kg] * SUM(SALES[Quantity]))
		),
		SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE(SUM(SALES[Quantity])))
	) + SUMX(SALES,[Add. costs]/SUM(SALES[Quantity])))

it represents a weighted average price for the product. When I put it on the date graph it sums the averages for that month for the postions instead of taking the average for a month. This is because that certain position is sold n timees per month, so it sums up all that n averages. How do I make it take the average of the averages? In case that is what I need.

Best reagrds,

Zenons

[v-zhenbw-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

[Anonymous]

@amitchandak This is how it looks in a table view. I have an average for every day. And in a date graph all of the averages sum up, but I need to take an average for those daily values, not sum.

[v-zhenbw-msft]

Hi @Anonymous ,

Do you want the average value of each position in the current month?

Or do you want to add up the daily average of each product, then calculate the average?

For example, the Sprats in oil EO (160 g) in January should be ( 2.94 + 7.01 + 3.08) / 3 = 4.34.

Maybe you can try the measure like this,

Measure =
DIVIDE (
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[Month Name],
            'Table'[Position],
            "sum", CALCULATE ( SUM ( 'Table'[value] ) )
        ),
        [sum]
    ),
    COUNT ( 'Table'[Position] )
)

Use the Summarize function to find the value of each Position, and then divide by Quantity.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous - Not entirely clear, but this looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.