+ Reply to Thread
Results 1 to 5 of 5

Removing Subtotals for Measure in Power Pivot

  1. #1
    Registered User
    Join Date
    10-30-2018
    Location
    ohio
    MS-Off Ver
    2016
    Posts
    6

    Exclamation Removing Subtotals for Measure in Power Pivot

    I am trying to add a text field into my Power Pivot table. To do this I created a measure and used the DAX formula:

    =IF(COUNTROWS(VALUES(Table1[Field]))>1,BLANK(),VALUES(Table1[Field]))

    This returns the right values for me BUT rows with only one value display that value in the subtotal. Is there a way around this? I know I could apply a conditional format formula but I would like to stay away from doing that and write it somewhere into the formula or something else I'm not thinking of.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019
    Posts
    386

    Re: Removing Subtotals for Measure in Power Pivot

    No attachment and no visualization of the expected result.
    Forgive me, but we will not guess what you expect.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    3,681

    Re: Removing Subtotals for Measure in Power Pivot

    Maybe you want HASONEFILTER.
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  4. #4
    Registered User
    Join Date
    10-30-2018
    Location
    ohio
    MS-Off Ver
    2016
    Posts
    6

    Re: Removing Subtotals for Measure in Power Pivot

    I will explain further.

    I am trying to sort employee IDs by Office and then Staff Level code subheadings. There are several columns of billing data in the values field area and then 3 columns of text values I need to add in at the end of the field area (This is how my boss wants it or I would just drag them into the 'Row' field and put them in the beginning and call it a day). The text values are Status, Dates(Text Formatting), and Notes/Comments.

    With the DAX formula =IF(COUNTROWS(VALUES(Table1[Status]))>1,BLANK(),VALUES(Table1[Status]))

    I was able to create a measure to display those text values. The problem is for some office and staff levels there is either only one employee or a couple employees with the same status description which causes the subtotal to display that text value. Ex: I have 3 employees under Cleveland Office-Staff Code 60 that are all listed as Active...This makes the subtotal display an 'Active' value instead of blank like other offices and staff codes with several employees and varying descriptions.

    I wanted to know if there was something I could put into the DAX formula to 'guard' the original formula or keep it from displaying in the subtotals. I was trying to stay away from the conditional format custom number format ";;;" way because they prefer I write it into the formula if possible.

    I attached a screenshot of just the subheadings and 'Status' column and where it's displaying a value in the subtotal section to get the general jist of it.capture.PNG

  5. #5
    Registered User
    Join Date
    10-30-2018
    Location
    ohio
    MS-Off Ver
    2016
    Posts
    6

    Re: Removing Subtotals for Measure in Power Pivot

    I forgot to add to this sooner but I wanted to thank, rorya. I didn't know anything about the newer filtered options until you mentioned it. I ended up with:

    =IF(ISFILTERED(Table1[ID]),VALUES(Table 1[Status]),BLANK())

    Had to play around with the formula for a little bit to get it just right but took care of my problem right away once I got it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1