Using Calculate as to achieve same results as sumifs

[wiz329]

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?

 

[wiz329]

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))

 

[wiz329]

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?

 

[wiz329]

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)