topic Re: Subtotal should give sumproduct result. How to create the formula in Power BI in DAX Commands and Tips

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

Marcus709 — Tue, 20 Aug 2019 14:08:20 GMT

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

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

Anonymous — Wed, 21 Aug 2019 00:43:06 GMT

[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

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

Marcus709 — Wed, 21 Aug 2019 14:13:21 GMT

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

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

Anonymous — Wed, 21 Aug 2019 17:59:39 GMT

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

Best
D.

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

Marcus709 — Fri, 23 Aug 2019 07:27:35 GMT

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

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

Marcus709 — Fri, 23 Aug 2019 07:32:19 GMT

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

Anonymous — Fri, 23 Aug 2019 09:30:46 GMT

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.

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

Marcus709 — Fri, 23 Aug 2019 09:52:53 GMT

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

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

Anonymous — Fri, 23 Aug 2019 11:54:37 GMT

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

Best
Darek

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

Marcus709 — Mon, 26 Aug 2019 15:32:21 GMT

Thanks a lot. That´s it.