how to sum a subtotal of a calculated measure istead of using the fromula in the subtotals

[powerpivotdaniel]

Hey,

This sounds like an easy problem, cause I just want the subtotal for each salesperson to be a sum of the customers below in the Bonus coloumn istead of a calculation. The Bonus measure formula is like this:

=(1-[Discount])*[Sum af Net]Very simpel stuff.
The problem is that the subtotals for the salesperson Daniel is 547.679 but if I summarize all the customer below him that summarize to 584.245, which is the number I want in the subtotals. Any idea how to do that? (the list below is not complete)

Rækkenavne	Gross	Net	Discount	Bonus
Callcenter	24.410.151	13.929.013	43%	8.004.784
Anne Nordhausen	2.683.363	1.710.105	35%	1.105.550
Charlotte Colditz Andersen	2.709.538	1.468.816	46%	799.211
Daniel Wenzel-Arnberg	1.315.602	848.195	35%	547.679
ASGER'S AUTO- & TRAILERSERVICE	5.944	4.458	25%	3.344
ATELIER BAR'ART	2.576	1.927	15%	1.638
AUTO BRANDT	2.100	2.100	0%	2.100
BALLERUP KRÆMMERFESTIVAL AF 2012	8.554	4.517	40%	2.710
BRDR. FUGLSANG VVS A/S	82.485	29.695	64%	10.690
COMPACT CLEAN APS	676	676	0%	676
CRAWFORD CENTER KØBENHAVN	42.067	28.257	33%	18.981
DANSK INDUSTRI- OG SKADESERVICE SJÆLLAND APS	16.200	16.200	0%	16.200
EJENDOMSMÆGLERFIRMAET HANS KORSGAARD	2.656	2.656	0%	2.656
ERGOMED CLINICAL RESEARCH LTD.	40.568	28.397	30%	19.878
FJORD-BUS A/S	3.270	3.270	0%	3.270
FLEX GULVSERVICE	700	700	0%	700
FRANCK GEOTEKNIK A/S	2.895	2.895	0%	2.895
FREDERIKSSUND BREMSESERVICE	13.898	6.483	53%	3.025
FYRSTENTHALS TAG-ENTREPRISE APS	43.439	30.086	31%	20.837

<colgroup><col style="width: 308pt;" width="411"> <col style="width: 59pt;" span="2" width="78"> <col style="width: 46pt;" width="61"> <col style="width: 53pt;" width="71"> </colgroup><tbody>
</tbody>

 

[MD610]

You need a SUMX to do this. How you set it up depends on how your underlying data tables are set up.

Do you have a separate customer table? If so, then try something like Bonus:=SUMX('Customer Table', (1-[Discount]) * [Sum of Net])

If you don't have a separate customer table and all your data is just stored in one table then try Bonus:=SUMX(VALUES('Table1'[Customers]), (1-[Discount]) * [Sum of Net])

SUMX basically evalutes an equation or another measure, individually for each row of the table specified in the first arguement. Then it remembers the result for each row and simply adds it all together at the end. Almost anytime you use a percentage or other ratio in a measure, you will need to use SUMX to get the subtotals to correctly appear as the sum of all underlying rows. Otherwise, your measure is just evaluating on salesperson aggregates. SUMX forces it to look at each row on its own.

SUMX is slow on very large tables, since it evaluates for each row, so it is best to always try and use it on smaller tables where possible.