Dax: for each pair of transactions: Net the two plus return zero of the smallest amount

[bgonen7]

I created a matrix. I am trying to create a variable dax formula to do the following:

1.For Each pair, identify the largest of the two and return the Net of the two amounts. (the red rectangle represents a pair with a common number "2110", then 2210 is another pair BUT then 2310 is not a pair).

2.For the smallest amount,,,retun "zero" or blank

3.If there are no pairs (for example 2310) , then return the existed amount that in the column "Amt doc Curr (AP)"

[v-cgao-msft · Accepted solution]

Hi @bgonen7 ,

Please refer to the sample file.

Highlighting the minimum and maximum values in a Power BI matrix

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[bgonen7 · Accepted solution]

There are three DAX formulas for this solution and I hope someone can help me make these DAX formulas more effeicient:

Formula1:

To sum each line of each pair:

Measure = CALCULATE(sum('model FactAPVendorOpenItem'[Amt Doc currency (AP)]),

ALL('model FactAPVendorOpenItem'[Vendor2],--to remove all filters

VALUES('model FactAPVendorOpenItem'[Pairs]))

Formula2:

Measure 2 =

var min1=CALCULATE([Measure]-([Amt Doc Curr (AP)]))--for the Max line,,get the variance between the max and Min values.

return IF([Amt Doc Curr (AP)]>min1,0,1)--if the record of the pair is the smallest then return Zero

Formula3:

Net Amount = -(IF([Measure 2]=0,0,CALCULATE([Measure]-([Amt Doc Curr (AP)]))-([Amt Doc Curr (AP)]))) -- if Measure2 equal zero, then return zero, Otherwise get the variance between the two amounts >>$149.2M minus 15.9M  which is $133.2M

[v-cgao-msft · Accepted solution]

Hi @bgonen7 ,

Please refer to the sample file.

Highlighting the minimum and maximum values in a Power BI matrix

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum