none
Aggregation by Key not by Text

    Question

  • Hey there,

    currently I have an issue with the aggregation in PowerPivot.

    Imagine this scenario:

    Fact-Table:

    CustID         SalesAmount

    1                  10

    2                  15

    3                  10

    1                  30

    Cust-Dim:

    CustID         Name

    1                  Bob

    2                  Jane

    3                  Bob (this is not the first Bob, it's another Bob)

    I'm trying to achieve an aggregation like this:

    Customer     SumOfSalesAmount

    Bob              40

    Jane             15

    Bob              10

    The system resolves it to:

    Customer     SumOfSalesAmount

    Bob              50

    Jane             15

    Is there any way to do this?

    "Keep Unique Rows" (to be found in the advanced-tab) only works within PowerView :-(

    Thanks in advance!


    Friday, July 19, 2013 11:46 AM

Answers

  • Hey there,

    currently I have an issue with the aggregation in PowerPivot.

    Imagine this scenario:

    Fact-Table:

    CustID         SalesAmount

    1                  10

    2                  15

    3                  10

    1                  30

    Cust-Dim:

    CustID         Name

    1                  Bob

    2                  Jane

    3                  Bob (this is not the first Bob, it's another Bob)

    I'm trying to achieve an aggregation like this:

    Customer     SumOfSalesAmount

    Bob              40

    Jane             15

    Bob              10

    The system resolves it to:

    Customer     SumOfSalesAmount

    Bob              50

    Jane             15

    Is there any way to do this?

    "Keep Unique Rows" (to be found in the advanced-tab) only works within PowerView :-(

    Thanks in advance!

    Hi Matthaeus,

    This behavior is by design. In this case, PowerPivot will aggreate all of data if we are using "Name" attribute(which contain same members for the attribute) to slice fact table data.

    As Larra suggested above, we can avoid the duplicate members for "Name" attribute to get expected result. Or we can add "CustId" attribute to the PowerPivot for data analysis.

    Thanks for your understanding.

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, December 02, 2013 7:47 AM
    Moderator

All replies

  • Hi Matthaeus,

    One way to solve the issue is to concatinate CustWholeName =

    =[Customer]& "-" &[CustID]

    ; Bob-1, Jane-2,Bob-3


    Larra

    Friday, July 19, 2013 1:21 PM
  • Hey Larra,

    thanks for your reply. I know that way, but I don't want to concatenate text + key.

    Plain Text is what I need :-/

    Friday, July 19, 2013 1:24 PM
  • No ideas?
    Tuesday, July 30, 2013 7:50 AM
  • Does anybody know how to do this with plain text?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, August 21, 2013 7:41 PM
    Owner
  • Matthaeus,
    Is this still an issue?
    Thanks!

    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, September 16, 2013 12:27 AM
    Owner
  • Hey Ed,

    yes this is still an issue! I haven't figured out any way for this :(

    Does anybody have any solution for this?

    edit:

    I appreciate Larra's answer, but it's not exactly what I've been looking for, that's why I unmarked it as answer

    Friday, October 11, 2013 11:27 AM
  • Hi,

    This may not be what your looking for either but perhaps you can add the CustID field to the Row Labels area of your PivotTable (before the Name field), and then hide that column from the sheet if you want.

    Sunday, October 13, 2013 10:25 AM
  • Hi,

    This may not be what your looking for either but perhaps you can add the CustID field to the Row Labels area of your PivotTable (before the Name field), and then hide that column from the sheet if you want.

    Hey there,

    I didn't get your plan. As you have probably read the solution of concatenating Key + Text is not an option.

    Are you saying to make the CustID the row identifier? ("Keep Unique Rows") Because that's what I tried.

    Thanks in advance!

    Thursday, October 17, 2013 7:42 AM
  • Hi,

    My suggestion does not involve concatenating any values or using a 'Keep Unique Rows' option.

    It is just to include an additional row label - the CustID field, before the Name field and optionally hide that column if you want. Perhaps this example may help make it clearer (column A on Sheet3 is the one I'm suggesting you can hide if you don't want to see it):

    http://sdrv.ms/GSSYYh

    Thursday, October 17, 2013 8:08 PM
  • Hi,

    My suggestion does not involve concatenating any values or using a 'Keep Unique Rows' option.

    It is just to include an additional row label - the CustID field, before the Name field and optionally hide that column if you want. Perhaps this example may help make it clearer (column A on Sheet3 is the one I'm suggesting you can hide if you don't want to see it):

    http://sdrv.ms/GSSYYh

    Hey circledchicken,

    thanks for your input!

    Unfortunately it - also - does solve my problem just partially. As soon as my Report-Viewer are doing some changes on Columns / Rows it might get lost and the effect is gone.

    I'm pretty sure this works in classic MDX-BI, but it was not yet "migrated" to PowerPivot-BI.
    Nevertheless I thank all of you for your support!

    Monday, October 21, 2013 3:34 PM
  • Ah ok, I'm not really familiar with MDX but anyway hopefully you will find a solution that works fully!

    Monday, October 21, 2013 9:11 PM
  • Matthaeus,

    We're looking for an SME to help with this.

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, November 29, 2013 11:41 PM
    Owner
  • Hey there,

    currently I have an issue with the aggregation in PowerPivot.

    Imagine this scenario:

    Fact-Table:

    CustID         SalesAmount

    1                  10

    2                  15

    3                  10

    1                  30

    Cust-Dim:

    CustID         Name

    1                  Bob

    2                  Jane

    3                  Bob (this is not the first Bob, it's another Bob)

    I'm trying to achieve an aggregation like this:

    Customer     SumOfSalesAmount

    Bob              40

    Jane             15

    Bob              10

    The system resolves it to:

    Customer     SumOfSalesAmount

    Bob              50

    Jane             15

    Is there any way to do this?

    "Keep Unique Rows" (to be found in the advanced-tab) only works within PowerView :-(

    Thanks in advance!

    Hi Matthaeus,

    This behavior is by design. In this case, PowerPivot will aggreate all of data if we are using "Name" attribute(which contain same members for the attribute) to slice fact table data.

    As Larra suggested above, we can avoid the duplicate members for "Name" attribute to get expected result. Or we can add "CustId" attribute to the PowerPivot for data analysis.

    Thanks for your understanding.

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, December 02, 2013 7:47 AM
    Moderator