Sumx? Calculate?

[gleblanc]

Hi,

I have a data set looking like this:

Cust__SalesRep___Invoice#___GL Account#_____Amount$
A_____John______21108______GrossSales_____1000.00
A_____John______21108______FreightCharges___133.00
B_____Eric_______21109______GrossSales______888.00

I need to calculate a 3% sale commission as follows:

1- For invoices with freight charges shown on invoice, commission is 3% of gross sales amount. By example, on invoice 21108, commission is (1133-133)*3%=30.00$

2- For invoices without freight charges shown on invoice (ie included in sale price), commission is 3% of 98% of gross sales amount. By example, on invoice 21109, commission is 888*98%*3%=26.11$

Obviously, my pivot table will summarize by customer and sales rep so I suppose I need a calculated column in my powerpivot data.

My problem is to determine if that "distinct" invoice has freight charge on it (another line) and how the commission must be calculated knowing that...

Thanks,

Guillaume

 

[masplin]

This might not be the most elegegant solution but it works.


Firstly in powerpivot create 2 new calculated columns firsly "Gross sales" with formula

=if([GL Account#]="grosssales",[Amount],blank())

Secondly "Freight Charges" formula

=if([GL Account#]="freightcharges",[Amount],blank())

This splits the costs into seperate columns so you can now work with them.

Now create your pivot table and create a new measure "Commission"

CALCULATE(if(sum(Table1[Freight Charges])>0,sum(Table1[Gross Sales])*0.03,sum(Table1[Gross Sales])*0.98*0.03))

Where "Table1" is the name of your table. This gives you this





<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="126">Row Invoice </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="108">Commission </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="126">21108 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="108">30 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="126">21109 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="108">26.1072 </td></tr> <tr><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="126">Grand Total </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" height="25.5" valign="bottom" width="108">56.64 </td></tr> </tbody></table>

I think you can then combine this at other levles, but havent tested it. Hope that helps

Mike


-- removed inline image ---

 

[ruve1k]

Add the following measures:

Sales:=CALCULATE(SUM(Amount),Table1[GL Account]="GrossSales")

Freight:=CALCULATE(SUM(Amount),Table1[GL Account]="FreightCharges")

Commission:=SUMX(VALUES(Table1[Invoice]),[Sales]*0.03*(1-([Freight]=0)*0.02))

 

[gleblanc]

Thank you for your replies.

Masplin: It worked but the pivot table didn't summarize correctly. For some reason, my subtotals were not right.

ruve1k: It is working perfectly. Can I ask you 2 quick questions:
1- Is VALUES the same as DISTINCT ? What is the difference ?
2- How can you translate in words this portion of the commission measure: *(1-([Freight]=0)*0.02) ?

Thanks again!

 

[ruve1k]

1. Yes, VALUES pretty much does the same thing that DISTINCT would do, the only difference would be if your data had blanks in Table1[Invoice]. VALUES would return the blank, while DISTINCT would not return the blank.
2. Consider the 2 possibilities:

(a) Let's say [Freight] does not equal zero, then [Freight]=0 returns FALSE. Then, when it calculates FALSE*0.02 the FALSE gets coerced into a zero, so we have 0*0.02 which returns zero. Then 1-0 returns 1. So it just multiplies the rest of the formula by 1 which does nothing. So you just end up with 3% of gross sales.

(b) Let's say [Freight] equals zero, then [Freight]=0 returns TRUE. Then, when it calculates TRUE*0.02 the TRUE gets coerced into a one, so we have 1*0.02 which returns 0.02. Then 1-0.02 returns 0.98. So it just multiplies the rest of the formula by 0.98. Since the order of multiplication doesn't matter, you can think of it like [Sales]*0.98*0.03. So you end up with 3% of 98% of gross sales.​

 

[ruve1k]

1. Yes, VALUES pretty much does the same thing that DISTINCT would do, the only difference would be if your data had blanks in Table1[Invoice]. VALUES would return the blank, while DISTINCT would not return the blank.

Actually, this does not apply to all blanks, only those that are missing values (unknown member) because of a referential integrity violation.
See the documentation on the VALUES function.