topic IF Statement on MATRIX returns correct value at top level but lower level is wrong in DAX Commands and Tips

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

GeorgeAiva — Fri, 28 Jun 2024 11:17:54 GMT

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

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

lbendlin — Fri, 28 Jun 2024 17:54:20 GMT

Remove [Value Type] from the matrix rows.

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

GeorgeAiva — Sat, 29 Jun 2024 12:32:05 GMT

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".

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

lbendlin — Sun, 30 Jun 2024 21:10:29 GMT

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

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

Anonymous — Mon, 01 Jul 2024 07:39:25 GMT

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.

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

GeorgeAiva — Mon, 01 Jul 2024 08:36:26 GMT

Thank you Anonymous.

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

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

Anonymous — Fri, 05 Jul 2024 09:26:17 GMT

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.

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

GeorgeAiva — Mon, 08 Jul 2024 07:29:31 GMT

this worked. Thank you so much!!!