Powerpivot Grand Total doen't reflects the values displayed in the column


  • Hello,

    i start saying that I'm new with PowerPivot and i'm not confident with the DAX formulas.

    i CREATED A pivot based on 2 tables. 1 called "Offers" with budget values and 1 called "ResourceActivities" with consumptions values. my situation is that i can have multiple resourceactivities (with their own consumptions) linked to the same offer (with 1 unique budget). i used the following formulas to compare consumptions and budgets:

    2.1) FTEestimation=CALCULATE(sum(Offers[TotalAmountManDays]),FILTER(ALL(Offers[ContentType]),COUNTROWS(FILTER(VALUES(ResourceActivities[ContentType]), Offers[ContentType] = ResourceActivities[ContentType])) <> 0))

    1) FTEconsumption=sum(ResourceActivities[TCSum])

    2.2) FTEbudget=if([IsBudgetValid],[FTEestimation],BLANK())

    My problem now is that while FTEconsuption Grand Total is consistent with the values i have filtered on the column, the FTEbudget grand total is completly diffeent from the sum of the amounts i can see above in the column. What i understood is that for this second column Grand Total the system is summing up all the values on the PowerPivot database column, and not the values displayed (as filtered by the formulas FTEestimation and FTEbudget). can anybody help me?

    Friday, January 03, 2014 4:04 PM


  • Hi Marco,

    Is it possible for you to provide a link to a copy of this workbook uploaded to SkyDrive so that we can take a look? If not, are you able to provide us with some test data, a description of the relationships between the tables (i.e. columns A from Table 1 links has a lookup to column B from table 2). Doing this will help myself or others in this forum to present you with one or more potential solutions.



    Sunday, January 05, 2014 11:39 AM

All replies