IF Statement on MATRIX returns correct value at top level but lower level is wrong

[GeorgeAiva]

Hi, 

I am trying to show the posted revenue for a product before and after the invoice is cleared. 

We have two value types:

Revenue Accrued: this is what we post prior to receive the invoice to have visibility of the incoming revenue

Revenue Invoice: this is the amount after the invoice is paid. 

The rule we apply is that if the "Revenue Invoice" is greater or equal to the "Revenue Accrued" then take the "Revenue Invoice", else take the "Revenue Accrued". 

I have created two measures for the InvoiceValue and the AccruedValue as showing below: 

Then Created a new measure "Revenue" where the rule I mentioned above is applied. 

The result is that the total value is correct (picking up the higher value) but at the Value type level it is showing both values. 

I want the Revenue Accrued to be blank or zero.

Similarly, if I select a previous month where the Accrued value is higher, I want only the Revenue Accrued type to show the value under the Revenue column and the "Revenue Invoice" to be zero or blank.

This is a simple example of a more complex report I am currently working on but the issue I am facing is the same. 

I appreciate any help to resolve this and any explanation of what is wrong.

Many thanks in advance,

George

[Anonymous]

Hi @GeorgeAiva ,

Based on my testing, please try the following methods again:

1.Create the sample table.

2.Create the new measure to filter the values.

Revenue = 
var accrued = CALCULATE(SUM(Data[Value]), Data[Value type] = "Revenue Accrued")
var invoice = CALCULATE(SUM(Data[Value]), Data[Value type] ="Revenue Invoice")
var _type = SELECTEDVALUE(Data[Value type])
RETURN
IF(invoice >= accrued && _type = "Revenue Invoice", invoice, 
    IF(invoice < accrued && _type = "Revenue Accrued", accrued, 0)
)

3.Drag the date column into the slicer visual.

4.Drag the measures into the matrix visual.

5.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GeorgeAiva]

Thank you @Anonymous. 

This works at the "Value type" level. How do I make it show the value at Product level and total? 

[Anonymous · Accepted solution]

Hi @GeorgeAiva ,

Please try the following methods again:

1.Create the measure to sum value.

Sum of value = SUM('Data'[Value])

2.Create the new measure to calculate the max value.

Max value = 
VAR _product = SELECTEDVALUE('Data'[Product])
var _tab = SUMMARIZE(ALLSELECTED('Data'),'Data'[Product],'Data'[Value type],"@sumofvalue",[Sum of value])
var _maxvalue = MAXX(FILTER(_tab,[Product]=_product),[@sumofvalue])
RETURN  
if(_maxvalue= [Sum of value],[Sum of value],0)

3.Drag Create the measure to calculate the Revenue.

Revenue = IF( ISINSCOPE('Data'[Value type]),[Max value],SUMX(GROUPBY('Data','Data'[Product],'Data'[Value type]),[Max value]))

4.Drag the measures into the matrix visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GeorgeAiva]

this worked. Thank you so much!!!

[lbendlin]

Remove [Value Type]  from the matrix rows.

[GeorgeAiva]

Thanks for your reply. I know this is an option but the users want to see the value type. There are more than one products and they want to see if the revenue for each product comes from the invoice or from the accrued value. The table will only show one revenue column and not the "Revenue accrued" or " Revenue invoice".

[lbendlin]

I would find that confusing. I cannot assist you with that ask. I hope someone else can help you further.