Forum Discussion
Formula for difference in subtotals when subtotal is blank
I'm trying to create a formula that results in the difference between 2 subtotals in a matrix. The formula I have now is working, but it does not work when the subtotal is blank.
Works when both subtotal values are present (QS4 Total - QS6Total = Target Remaining):
Does not work when subtotal value is blank: (I would like a value displayed in Target Remaining; 2-(blank) = 2, (blank)-3 = -3, etc.)
I don't think there is a way to change the raw data values to 0 so that it performs the calculation since the blank values represent customer who do not have the product listed and there is no data line for that, only lines for customers who have either QS4 or QS6. My formula is below.
3 Replies
- lbendlin
Super User
Read about COALESCE()
Thank you, Ibendlin. I did create a new COALESCE measure to turn blank values to 0, but my above measure still will not calculate the difference when either column has a value of 0. In my measure above, it will not allow me to bring in my new COALESCE measure.
For example, when I try to replace ...SUM('Install Hist'[Quanityt]), with SUM('Install Hist'[NEW COALESCE MEASURE]) it will not bring that new measure in. Is there a way to add the COALESCE measure into my measure above so that it still calculates the difference in values even when one of the values is 0?
I would expect a result where:
If QS4 = 0 and QS6 = 2, the result in Target Remaining = -2
If QS4 = 2 and QS6 = 0, the result in Target Remaining = 2
- lbendlin
To report on things that are not there you need to use disconnected tables and/or crossjoins
