Subtotal should give sumproduct result. How to create the formula in Power BI

[Marcus709]

Hi,

I have a serious problem in dealing with the results of  a user defined formula on the subtotal level.  The values there are simply the sum of the individual values of the grouped (subtotal) entries.

Now I will be more specific: I added two screenshots (one excel, one Power BI) for a better illustration .

Table: tabelle1

units: units

price: price

artname: the individual names of articles (here: pet names)

sold: units x price

avgprice: sold/units.

For “avgprice” the sumproduct (sold/units) should give me the wanted results for subtotals, too. But this only works in excel-pivot, not in power BI. Instead of the proper result 5,66 I get  16,99 in Power BI for "type1".

I hope someone has a helping idea

Marcus

[Anonymous]

[Total Sales] =
	sumx (
		tabelle1,
		tabelle1[sold] * tabelle1[units]
	)

[Total Units] = sum ( tabelle1[units] )
	
[Average Price] =
var __sales = [Total Sales]
var __units = [Total Units]
var __avg = divide( __sales, __units)
return
	__avg

Best

Darek

[Marcus709]

Hi Darek,

at first thanks.

I created the new measures "total sales" and "total units".

But sorry, I don´t get the last part.  If I try to put it as one new measure [Average Price] as a formula in one line of the editor, I get a syntax warning. Can you please explain a little further?

Kind regards

Marcus

[Anonymous]

I can't see a picture of this warning...

Best
D.

[Marcus709]

Hi,

sorry, yeasterday I was busy.

 I added a screenshot. I tried to enter it as a formula with "new measure". Probably you had something other in mind where to insert the code? Marcus

[Marcus709]

[Anonymous]

Why are you not formatting your code PROPERLY? No wonder you can't get this right... A formula should be well formatted and not entered as a simple stream of characters... Please go to https://www.sqlbi.com/articles/rules-for-dax-code-formatting/ and learn to write good code. In the editor you obtain a new line by pressing SHIFT+Enter (or CTRL+Enter).

Best
D.

[Marcus709]

...Now you find it in the formatted way. Can you tell me, where the problem still lies?

[Anonymous · Accepted solution]

These are 3 different measures. You cannot enter them all in one go. Each measure must be entered on its own.

Best
Darek

[Marcus709]

Thanks a lot. That´s it.