Forum Discussion

Lightice83's avatar
Lightice83
Icon for Helper I rankHelper I
3 years ago

Isolate/Identify the Grand Total row in Power Pivot

I'm aware that I need to create a different formula to obtain the correct Grand Total for a Pivot table for a DAX measure but I cannot identify the Grand Total row with my formula

 

I am using the below but it is returning TRUE for all rows even the Grand Total

HASONEVALUE(Program_LKP[program])

Should I be referencing the column or row dimension & does it matter that the row dimensions are nested? 

 

Need Help

3 Replies

  • Mikelytics's avatar
    Mikelytics
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi Lightice83,

     

    Can you please try:

     

    HASONEVALUE(Program_LKP[1st_Intake_Cohort])

     

    Best regards

    Michael

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.

    • Lightice83's avatar
      Lightice83
      Icon for Helper I rankHelper I
      3 years ago

      Thanks Mikelytics but the below is not a Valid column reference as they are referring to 2 different dimensions

      Program_LKP[1st_Intake_Cohort]

       

    • Lightice83's avatar
      Lightice83
      Icon for Helper I rankHelper I
      3 years ago

      Mikelytics I actually got the FALSE tag to work using my orginal formula. It works when the 'Program' dimension is physically unfiltered in the PivotTable(It is a bit grainy above but you can see that it is filtered on 'GC175').

       

      When it is physically filtered then the 'Grand Total' row will return TRUE but if unfiltered then it returns FALSE