Results 1 to 4 of 4

Thread: Using Calculate as to achieve same results as sumifs
Thanks Thanks: 0 Likes Likes: 0

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

    Default Using Calculate as to achieve same results as sumifs

    Hi guys. I apologize if there's something already out there that answers my question. I'm new to the world of DAX, and I wasn't able to find my exact situation readily. This is a fairly simple scenario:

    I have a table of transaction data, which has multiple line items for each part. So a lot of times, I'm doing aggregation on this data to the "part-level".

    I also have a lookup table ("part-master") which contains characteristics about each part, and higher level part aggregations (let's call them "part-groups"). Now, let's assume not every part in the "part-master" actually has transactions associated with it in the base data table ("transactions"), and let's assume that there are some zero-value transactions (for the purposes of this formula, I know it doesn't make a ton of sense.)

    Basically, I want a pivot table that counts the total number of parts with revenue (a column in the "transaction" table) for each "part-group", and also sums that revenue total. The second measure is easy. I can't quite figure out the first one.

    Essentially, I'm trying to create a measure that says, "Count up every part in each group (I'm thinking from the"part-master" lookup table) that has positive revenue associated with it in the transactions data."

    So basically the equivalent of an excel "COUNTIFS", just across table relationships. Does that make sense?

  2. #2
    New Member
    Join Date
    Jun 2014
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Calculate as to achieve same results as sumifs

    I finally hit on the right formula:

    I started with a count of the rows in the part master table, and an explicit measure to sum revenue:
    Number of Parts:= COUNTROWS(PartMaster)
    Total Revenue:= SUM(Transactions[Resale Revenue])

    Then I wrote another measure to count only rows for which there was revenue:
    Number of Revenue-Generating Parts:= CALCULATE([Number of Parts], FILTER(Product Master, [Total Revenue]>0))

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

    Default Re: Using Calculate as to achieve same results as sumifs

    As a follow up to this, I was trying to calculate the % of total for each group. For this I used:

    % of Revenue-Generating Parts:=DIVIDE ( [Number of Revenue-Generating Parts] , ALL(ProductMaster[Group Name]) ) , 0)

    However, I'd like to make this measure more dynamic. It works if I've got "Group Name" on the rows of the pivot. But what if I have a different type of part grouping on the rows? I'd like a measure that basically always shows % of total column (i.e. with the row filter context removed). Is that possible?

  4. #4
    New Member
    Join Date
    Jun 2014
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Calculate as to achieve same results as sumifs

    EDIT to previous post:

    % of Revenue-Generating Parts:=DIVIDE ( [Number of Revenue-Generating Parts] , CALCULATE([Number of Revenue-Generating Parts], ALL(ProductMaster[Group Name]) ) , 0)

Some videos you may like

User Tag List

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
  •