Answered by:
Add New Measures in Power Pivot
Question

I am trying to add new measures for
% of total Sales Revenue (by PayPal, ClickAndBuy, Voucher, TopUp)
% of total Transactions (by PayPal, ClickAndBuy, Voucher, TopUp)
in Power Pivot, Can anybody help me on this please?
Thank you
 Edited by nthushy Wednesday, February 13, 2013 3:35 PM
Monday, February 11, 2013 3:06 PM
Answers

Looks like you got it. You just missed one closing paren for SUM in the denominator. Try this:
=SUM('Sheet1'[Sales Price]) / CALCULATE(SUM('Sheet1'[Sales Price]) ,ALL('Sheet1'[Payment Mode]) )
Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com
 Marked as answer by nthushy Thursday, February 14, 2013 9:46 AM
Tuesday, February 12, 2013 8:30 PMAnswerer 
Glad that helped.
Assuming each row is a single transaction, you can just use COUNTROWS over the table for the transaction count. Like this:
=COUNTROWS('Sheet1') / CALCULATE(COUNTROWS('Sheet1') ,ALL('Sheet1'[Payment Mode]) )
Looks like you already have the [Transactions] measure defined in the screen shot above. Check to see exactly what that calc is doing, and just work it in where the SUM(Table[field]) is in the othere % measure that is working for you.
And for average sales price per transaction (again, assuming each row is a single transaction), you can just use the AVERAGE() function like this:
=AVERAGE('Sheet1'[Sales Price])
Let me know if that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com Edited by Brent GreenwoodEditor Wednesday, February 13, 2013 1:24 AM
 Marked as answer by nthushy Thursday, February 14, 2013 9:46 AM
Wednesday, February 13, 2013 1:21 AMAnswerer
All replies

this should help:
http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=69
=Sum(Fact[Sales])/Calculate(Fact[Sales],All(Fact))
if you only want to remove specific filters and not All() filters from your facttable you may also remove the filter from every table individually:
=Sum(Fact[Sales])/Calculate(Fact[Sales],All(PayPal), All(ClickAndBuy), All(Voucher), All(TopUp))
hth,
gerhard www.pmOne.com 
Monday, February 11, 2013 6:33 PMAnswerer 
T.Nathan 
ALL() takes a table or column reference. That's what Gerhard meant above. So you can just pass ALL() your Payment Mode column to release that filter and get all payment modes for the denominator of your percentage calc measures. Assuming you have Payment Mode in a single table called "Fact" (or a related table called same), it would be something like:
= [Sales Revenue]
/ CALCULATE([Sales Revenue], All(Fact[Payment Mode]))And just substitute [Transactions] for [Sales Revenue] for the other % measure. ALL(Fact[Payment Mode]) will release only that filter and still respect the filters on country and month.
Let us know if that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com
 Edited by Brent GreenwoodEditor Tuesday, February 12, 2013 4:38 AM
Tuesday, February 12, 2013 4:36 AMAnswerer 
[Sales Revenue] was the measure name from your previous screen shot. Perhaps that's an alias for the pivot?
Regardless, you must update the formula with the specifics from your model (table name, column name, measure name, etc.).
A more generic formula (that you will have to plug your model's references into) would be:
=Sum('YourTableName'[YourColumnNameToBeSummed])
/ CALCULATE(SUM('YourTableName'[YourColumnNameToBeSummed]
,ALL('YourTableName'[YourColumnNameToRemoveFilterFrom])
)Please adapt this to your model and give it a try. If you still have issues, you'll need to provide the table name (or a screen shot of the diagram view of your model from the powerpivot window).
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com Edited by Brent GreenwoodEditor Tuesday, February 12, 2013 12:59 PM
Tuesday, February 12, 2013 12:57 PMAnswerer 
please post a screen shot of the measure definition (DAX expression) and the error
Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com
 Edited by Brent GreenwoodEditor Tuesday, February 12, 2013 5:56 PM
Tuesday, February 12, 2013 5:26 PMAnswerer 
Looks like you got it. You just missed one closing paren for SUM in the denominator. Try this:
=SUM('Sheet1'[Sales Price]) / CALCULATE(SUM('Sheet1'[Sales Price]) ,ALL('Sheet1'[Payment Mode]) )
Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com
 Marked as answer by nthushy Thursday, February 14, 2013 9:46 AM
Tuesday, February 12, 2013 8:30 PMAnswerer 
Glad that helped.
Assuming each row is a single transaction, you can just use COUNTROWS over the table for the transaction count. Like this:
=COUNTROWS('Sheet1') / CALCULATE(COUNTROWS('Sheet1') ,ALL('Sheet1'[Payment Mode]) )
Looks like you already have the [Transactions] measure defined in the screen shot above. Check to see exactly what that calc is doing, and just work it in where the SUM(Table[field]) is in the othere % measure that is working for you.
And for average sales price per transaction (again, assuming each row is a single transaction), you can just use the AVERAGE() function like this:
=AVERAGE('Sheet1'[Sales Price])
Let me know if that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com Edited by Brent GreenwoodEditor Wednesday, February 13, 2013 1:24 AM
 Marked as answer by nthushy Thursday, February 14, 2013 9:46 AM
Wednesday, February 13, 2013 1:21 AMAnswerer 
It's working fine. Thank you very Much.
And thank you again for your time and effort.
Nice one : )
Wednesday, February 13, 2013 3:28 PM 
No worries. Please remember to mark correct answers. Thanks.
Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com
Wednesday, February 13, 2013 8:03 PMAnswerer