Results 1 to 5 of 5

Thread: Calculate with rows filter
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate with rows filter

    Hi All,
    Very new to Power tools . i have been experimenting on this issue which i can deal in Sumifs.

    How can I show in Pivot table that Mark has 30 total, Ricky 15 Total, Peter 18 Total and Gino has 5?
    10 ID01 Mark
    15 ID02 Ricky
    20 ID01 Mark
    10 ID03 Peter
    5 ID04 Gino
    8 ID03 Peter


    Much thanks
    Last edited by reggieneo; Apr 10th, 2019 at 04:06 AM.

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,170
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate with rows filter

    It depends on your table structure. If all 3 columns above are in the same table, just drags the ID and Name to rows of the pivot, drags the first column to values, and change he pivot table layout to what you need
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    New Member
    Join Date
    Jun 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate with rows filter

    Hi Matt, thank you for responding to my issue and sorry for late reply as i was on leave from office. I can only access the file to explain further from office. I have a unique ID Table ("IDNumber")on another workbook, I can get the total value of all the respective people from Column A thru this formula:calculate(Sum(Page1[Working Hrs]), ALLEXCEPT(Page1, IDnumber[Number]).but I would like to have a measure or calculated column to get total for each IDNumber. this formula is not working:=calculate(Sum(Page1[Working Hrs), ALLEXCEPT( IDnumber, IDnumber[Number])). I have the relationship setup for between these tables too. much thanks.

  4. #4
    Board Regular
    Join Date
    Jan 2012
    Posts
    854
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate with rows filter

    Does that make a difference? IF they are joined in your model, you can pull the name and the hours into your pivot, and it will total for you, no need for that measure as far as I can see.

  5. #5
    New Member
    Join Date
    Jun 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate with rows filter

    yes, I have been confused with reiterated results. but I see already. thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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