locked
Measurement in the Tablue RRS feed

  • Question

  • Goal:
    Have a measurement for each PK_test row based on sum of all its quantity from productNumber.


    PK_test        qty     productNumber         
    -----------------------------------------------
    1                   5              asdf
    2                   4              asdf
    3                   9              sdfg
    4                   1              sdfg
    5                   1               lkj
    6                   6               lkj
    7                   3               lkj
    8                   3               mnb


    To make it more easier to understand the result will be of the mesure "m"

    PK_test        qty    productNumber          m         
    -----------------------------------------------
    1                     5                      asdf          9
    2                     4                      asdf          9      
    3                     9                      sdfg        10
    4                     1                      sdfg        10
    5                     1                         lkj        10
    6                     6                         lkj        10
    7                     3                         lkj        10
    8                     3                      mnb          3


    Problem:
    How should I create a calculated measure "m"?


    Info:
    - No SQl code
    - I'm using SSAS tabular
    - The value shall not be changed when you select one or many column in dimension table(s). In order word, the value will not be altered based on selection.

    Sunday, June 9, 2013 9:36 AM

Answers

  • Several ways to approach this.  One way is to use CALCULATE and ALLEXCEPT to group by the productNumber like this:

    =CALCULATE(SUM(YourTable[qty])
                         ,ALLEXCEPT(YourTable, YourTable[productNumber])
                         )
    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    • Proposed as answer by powerpivotq Tuesday, June 11, 2013 1:18 PM
    • Marked as answer by Elvis Long Monday, June 17, 2013 2:08 AM
    Tuesday, June 11, 2013 2:04 AM
    Answerer

All replies

  • Several ways to approach this.  One way is to use CALCULATE and ALLEXCEPT to group by the productNumber like this:

    =CALCULATE(SUM(YourTable[qty])
                         ,ALLEXCEPT(YourTable, YourTable[productNumber])
                         )
    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    • Proposed as answer by powerpivotq Tuesday, June 11, 2013 1:18 PM
    • Marked as answer by Elvis Long Monday, June 17, 2013 2:08 AM
    Tuesday, June 11, 2013 2:04 AM
    Answerer
  • Several ways to approach this.  One way is to use CALCULATE and ALLEXCEPT to group by the productNumber like this:

    =CALCULATE(SUM(YourTable[qty])
                         ,ALLEXCEPT(YourTable, YourTable[productNumber])
                         )
    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    works great!!! can you explain why it works in your own words?! that would be amazing! Want to understand the intuition of using ALLEXCEPT.

    thxs!!!

    Tuesday, June 11, 2013 1:20 PM