Sum if Product Type="MRC", but Product Type is in a Related Table

DAX DAX Calculations
1

I have revenue in one table, the DETAILS table, and the product detail in a related table, the PRODUCT ID table. They are related by the field Product ID and ID. I am trying to sum all MRC (monthly recurring revenue) for a given month.

This is what I’ve tried but I get blank as a result.
MRC Revenue 2 =
CALCULATE(
Sum(Details[Revenue]),FILTER(ProductID,CONTAINS(ProductID,ProductID[Type],“MRC”)))

Here’s a sample of my data:
ProductID Table:
image

Revenue detail table:
image

I would appreciate any help to make this work correctly.

Thank you

2

@ScottTPA,

I think this will get you what you need. Almost identical to your measure, but uses CONTAINSSTRING() instead of CONTAINS().

Total MRC Revenue = 

CALCULATE(
    [Total Revenue],
    FILTER(
        'Product',
        CONTAINSSTRING( 'Product'[Type], "MRC" )
    )
)

image

3

That worked, thank you. One more question that I hope you can help with: In my visual, I want to show only those customers who had a decrease from prior month in total. The individual line items may increase but if there is a net decrease, I want to show that customer and all of the line items associated with it. To get the change from prior month, I did this:
MRC Revenue - Prior Month = CALCULATE([MRC Revenue - Current Period],DATEADD(‘Date’[Date],-1,MONTH))

MRC Revenue Inc/(Dec) vs Prior Month = -[MRC Revenue - Prior Month] + [MRC Revenue - Current Period]

This calculation works, but I don’t know how to show those customer where the total decreases. When I try to filter for decreases, my result only includes the individual product lines that decreased and ignores the others.

4

@ScottTPA,

Sure - glad to work on this second question with you. I have some initial thoughts, but need to test them out. Can you please post your PBIX file w/ any confidential info masked?

Thanks.

  • Brian
5

Thank you. The file is attached. TestCo.pbix (127.5 KB)

6

@ScottTPA,

I think you were super close on this one – probably only one line of DAX away from the solution. The key is removing the filter on Product ID, so that the delta computed in total by Name. Here’s the revised measure:

Total MRC Delta Neg Only = 

VAR Delta =
CALCULATE(
    [MRC Revenue Inc/(Dec) vs Prior Month],
    REMOVEFILTERS( 'Product'[ID] )
)

RETURN
IF(
    Delta < 0,
    [MRC Revenue Inc/(Dec) vs Prior Month],
    BLANK()
)

image
image.png743×415 14.2 KB

Full solution file posted below.

1 Like
7

Thank you. That worked perfectly and I appreciate the explanation of your logic to get there. I understand now. I wouldn’t have thought of that because I am relatively early in my understanding of Dax. I greatly appreciate your help with this.