locked
Sum of Averages RRS feed

  • Question

  • I have a Sales and Target table as shown below. I have a relationship setup between the Sub-Org field in the 2 tables.

    I want a pivot table as shown further down with the Sales TCV and Sales Target but am not able to get the target as shown in the 3rd column (highlighted in green). How do I do that?

    Tuesday, April 12, 2016 8:04 AM

Answers

  • There are a couple of DAX functions you can use to determine where you are in a pivot table, like ISFILTERED, ISCROSSFILTERED, HASONEVALUE, and HASONEFILTER.

    The problem is that in a row for Account, both Org and Sub-org are (cross)filtered and have one value, so these functions will not help you much if you don't want to reference Account, and other columns.

    Maybe you can test on equality of other results, e.g.

    IF([Sales]=CALCULATE([Sales],ALLEXCEPT(Sales,Sales[Org];Sales[SubOg]); ... )

    but you'll run into problems, like when [sales] is 0 for a [Org].

    • Proposed as answer by Charlie Liao Friday, April 22, 2016 2:54 AM
    • Marked as answer by Charlie Liao Sunday, April 24, 2016 7:39 AM
    Friday, April 15, 2016 12:30 PM
    Answerer

All replies

  • You can use COMPUTE BY ORG  to achieve this. Have a look at the samples at 

    https://technet.microsoft.com/de-de/library/ms181708(v=sql.105).aspx

    hth

    Gregor Stefka

    --

    Businessprocessautomation with the EWS SQL Interface SQLExchangeSync

    EDIT: Sorry, i was in the SQL World, not power Pivot. You can use Calculate() with filter/earlier:

    http://www.powerpivotpro.com/2013/07/writing-a-subtotal-calc-column-aka-the-simplest-use-of-the-earlier-function/

    Tuesday, April 12, 2016 8:12 AM
  • You can use ISFILTERED() to check where you are in the pivot table. On the Sub-org rows, where you want to show targets, the sub-orgs are filtered but the accounts are not. So the calculation for target would be (as a calculated field):

    Target I want to show:=IF(NOT(ISFILTERED(Sales[Account])),SUM(Target[Target]))

    Tuesday, April 12, 2016 1:12 PM
    Answerer
  • Hi VivDev,

    In a Pivot table, the sub total or grand total row was sum up the values in detail row automatically. In your scenario, you need to display blank on detail rows, and corresponding value on total row from another table, I am afraid this cannot be done currently.

    To work around this issue, you can calculate the target for account level based on the TCV value. I have tested on my local environment, here is the sample DAX for you reference.
    =LOOKUPVALUE(Target[Target],Target[Sub-Org],Sales[Sub-Org])*Sales[TCV]/CALCULATE(sum(Sales[TCV]),ALLEXCEPT(Sales,Sales[Org],Sales[Sub-Org]))

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, April 13, 2016 2:37 AM
  • You can use ISFILTERED() to check where you are in the pivot table. On the Sub-org rows, where you want to show targets, the sub-orgs are filtered but the accounts are not. So the calculation for target would be (as a calculated field):

    Target I want to show:=IF(NOT(ISFILTERED(Sales[Account])),SUM(Target[Target]))

    Thanks. This worked beautifully. However, is there a way I can make it such that Targets will be shown when either Org or Sub-Org are shown and not in all other cases. I have a lot of other fields that are used in the pivots so instead of putting each of them in the formula, it would be great if I could have a condition based on the Org and Sub-Org. 
    Thursday, April 14, 2016 10:02 AM
  • There are a couple of DAX functions you can use to determine where you are in a pivot table, like ISFILTERED, ISCROSSFILTERED, HASONEVALUE, and HASONEFILTER.

    The problem is that in a row for Account, both Org and Sub-org are (cross)filtered and have one value, so these functions will not help you much if you don't want to reference Account, and other columns.

    Maybe you can test on equality of other results, e.g.

    IF([Sales]=CALCULATE([Sales],ALLEXCEPT(Sales,Sales[Org];Sales[SubOg]); ... )

    but you'll run into problems, like when [sales] is 0 for a [Org].

    • Proposed as answer by Charlie Liao Friday, April 22, 2016 2:54 AM
    • Marked as answer by Charlie Liao Sunday, April 24, 2016 7:39 AM
    Friday, April 15, 2016 12:30 PM
    Answerer