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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Marcus709
Frequent Visitor

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

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 .Doku_BI.PNGdoku_excel.PNG

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

Best
Darek

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

[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

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
Not applicable

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

Best
D.

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

doku_error.PNG

Anonymous
Not applicable

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.

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

doku_error_formatted.PNG

Anonymous
Not applicable

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

Best
Darek

Thanks a lot. That´s it.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.