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

1. 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?  Reply With Quote

2. 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))  Reply With Quote

3. 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?  Reply With Quote

4. 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)  Reply With Quote

User Tag List Posting Permissions

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