Logic Formula - Max value of two columns and two tables with same Reference row ID

[Anonymous]

Hi,

Can someone help me with a formula soultion. 🙂

I have these two tables of payments the POS & AR. They are connected by this Reference number.

I have a column in the POS Table that looks up if they match or not with this formula:

Matched Ref Number = IF(ISBLANK(LOOKUPVALUE('Point of Sale Tickets'[Ref Number],'Point of Sale Tickets'[Ref Number],'AR Invoice Detail - PMT Fact'[Ref Number])) , "Not Matched",

"Matched"

)

I want to return the Max of the two tables dollar amounts (AR$ or POS$ which ever is higher) Looking at the Reference ID. I cant figure this logic out. Thanks so much!

AR Table

POS Table

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether you can get what you want. It can achieved by creating a measure or calculated column as below:

Measure:

Max amount = 
VAR _posamount =
    SELECTEDVALUE ( 'Point of Sale Tickets'[Cash-POS] )
VAR _currefnumber =
    SELECTEDVALUE ( 'Point of Sale Tickets'[Ref Number] )
VAR _armount =
    CALCULATE (
        MAX ( 'AR Invoice Detail - PMT Fact'[AR Amount] ),
        FILTER (
            'AR Invoice Detail - PMT Fact',
            'AR Invoice Detail - PMT Fact'[Ref Number] = _currefnumber
        )
    )
RETURN
    IF (
        ISBLANK ( _armount ),
        BLANK (),
        IF ( _armount > _posamount, _armount, _posamount )
    )

Calculated column:

Column = 
VAR _armount =
    CALCULATE (
        MAX ( 'AR Invoice Detail - PMT Fact'[AR Amount] ),
        FILTER (
            'AR Invoice Detail - PMT Fact',
            'AR Invoice Detail - PMT Fact'[Ref Number] = EARLIER('Point of Sale Tickets'[Ref Number])
        )
    )
RETURN
    IF (
        ISBLANK ( _armount ),
        BLANK (),
        IF ( _armount > 'Point of Sale Tickets'[Cash-POS], _armount, 'Point of Sale Tickets'[Cash-POS] )
    )

Best Regards

[amitchandak]

@Anonymous , It better to have a common Ref table. Join it to the ref no of these two tables and then  try a measure like

Sumx(values(Ref[Ref]), calculate(if(sum(Table1[AR Amount]) > Sum(Table2[Cash POS]) , sum(Table1[AR Amount]), Sum(Table2[Cash POS]))))