locked
Add New Measures in Power Pivot RRS feed

  • 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 PM
    Answerer
  • 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


    Wednesday, February 13, 2013 1:21 AM
    Answerer

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 fact-table 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 PM
    Answerer
  • 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



    Tuesday, February 12, 2013 4:36 AM
    Answerer
  • [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


    Tuesday, February 12, 2013 12:57 PM
    Answerer
  • 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


    Tuesday, February 12, 2013 5:26 PM
    Answerer
  • 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 PM
    Answerer
  • 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


    Wednesday, February 13, 2013 1:21 AM
    Answerer
  • 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 PM
    Answerer