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

 

[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.