Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
[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
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
...Now you find it in the formatted way. Can you tell me, where the problem still lies?
Thanks a lot. That´s it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |