My problem is twofold, first, for each customer, I need to sum all amounts with MRC in the Product Type. I did this using the attached data and this measure:
Next, I need to show any customers where total MRC went from a positive or negative amount to $0. In other words, if the customer was billed last month or credited last month and now they have no revenue, then they must have canceled. We want to see all customers who canceled in the current month as selected using a month/year slicer.
To do this part, I used the ‘Filters on This Visual’ and set MRC Prior Revenue is not 0 and MRC Revenue - Current Month is 0.
The measure for MRC Prior Revenue is MRC Revenue - Prior Month = CALCULATE([MRC Revenue - Current Month],DATEADD(‘Date’[Date],-1,MONTH))
This works except, my result is showing customers where the individual service types went from having revenue to $0 revenue. For example, if “Rzcurrong - MRC - RzachUC” had a balance and now has a zero, my result shows this. I need the result to show only when the TOTAL OF ALL revenue that has MRC in the product type was positive or negative and is now $0.
Update, I figured out my problem. I was including Product Type as a field in my ROWS for the visualization. This was causing each row to be evaluated.
If you know of a better way to solve this without using a measure instead of the ‘Filters on This Visual’ feature, please let me know. Otherwise, I think I don’t need additional help.
If you prefer not to use the visual filters pane, you can do this solely with DAX, using a SWITCH( TRUE() ) construct to blank out all the values that don’t meet your filter conditions:
MRC With Blanks =
VAR MRCLessCurrent = [MRC Revenue - Current Month]
VAR MRCLessPrior = [MRC Revenue - Prior Month]
RETURN
SWITCH (
TRUE (),
MRCLessPrior <> 0, BLANK (),
MRCLessCurrent = 0, BLANK (),
MRCLessCurrent
)
Thank you Brian, I think this would work except I have one problem. Each ‘Revenue’ amount is made up of the bill with several line items, each line item is a Product Type. This is picking up any line item with a $0 in the current month and not in the prior. Is there a way to make it sum all revenue for the customer in the month and then compare current and prior? This is a big problem because it affects my calculation for almost everything including New Customer Sales, etc. The formulas are basing everything off of comparisons by product type, not all sales to that customer
Blockquote
Ben, I did it in a very inelegant way. I basically just show the Customer and no detail in my grid visualization. When I try to add detail, it doesn’t work. See my follow-up question from a moment ago that asks about this.
Can you please post your PBIX file? What you’re describing certainly sounds doable, but to provide a specific solution, we’ll need to see your data model, the visualization you currently have, etc.
Thank you, I can’t post the pbix file because it has client details in it. I can provide an example, attached, of the data that has two sample customers. You can see in this, the customers have multiple products that they may or may not be billed for on a given date. powerbi post data.xlsx (21.5 KB)
Is this what you’re looking for? I’ve read through your requirements a few times, and I think it is, but if not let me know – this is easily modifiable. Full solution file posted below.
Thank you. That is helpful, but what I need to do is sum everything for the customer for that particular date and then compare or perform functions on it. Right now, when I try to do something like ‘new sales in the current period’ the formula looks at the product and counts new sales for that customer/product combo. I think what I need is a table function that pulls the customer and amounts into a separate table and ignores the product. I am not that familiar with table functions yet though I am not certain if that is the correct approach.
Update… I got it to work using SUMMARIZE by adding a New Table with this as the table formula,
Details by Customer =
SUMMARIZE (
Details,
Details[Customer_ID],Details[Month],
“Total Revenue by Customer”, SUM ( Details[Revenue] )
)
