Column total when using DIVIDE in rows not calculating correctly

[Anonymous]

Hi all,

Happy New year to everyone.

Have a question regarding the column total not working as expected when using DIVIDE to calculate the row.

Let me explain

Each week, we have £££ sales and # of units sold. We calculate a 3rd column for Rate

Measure: Rate = DIVIDE( sales, [units Sold] )

Please see picture below to explain a little further

If we add all the values for Rate we get 15,818. However, the Total row is doing this maths, 42,256/31.57 = 1,338

How can we keep the row DIVIDE but sum the results??

[Anonymous]

Thank you both. Works perfectly... I should have remembered but thanks

[littlemojopuppy]

Try this...

IF(
	ISFILTERED(WeekNumber),
	DIVIDE(
		Sales,
		UnitsSold,
		BLANK()
	),
	SUMX(
		TableName,
		DIVIDE(
			Sales,
			UnitsSold,
			BLANK()
		)
	)
)

I know nothing about your data model so the table, field and measure names are guesses.  Substitute the real table/field/measure names where appropriate.

[parry2k · Accepted solution]

@Anonymous assuming [Sales] and [Units Sold] are existing measure, add a new measure 

Rate Meassure = SUMX ( VALUES ( Table[WeekNo] ), DIVIDE( [sales], [units Sold] ) )

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡