Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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!
Solved! Go to 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
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
@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]))))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
79 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |