cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KrisT Frequent Visitor
Frequent Visitor

Show value associated with a calculation in another column

Hi All,
 
I'm having trouble with displaying a value associated with a calculation in another column. The formula below shows the maximum Instructing Client share (based on the Order Value (excl. VAT)) for a particular Year/Quarter/Month determined by a Date slicer.
 
Largest Instructing Client Share Result =
MAXX(
KEEPFILTERS(
VALUES('ForecaseAndActual'[Instructing Client])),CALCULATE(
SUM('ForecaseAndActual'[Order Amount (excl. VAT)]),USERELATIONSHIP('Date'[Date],ForecaseAndActual[Order Received]
)))
 
I want to show the associated Instructing Client that the resulting value is displaying.
 
Data is formatted as follows:
 
Instructing ClientOrder ReceivedOrder Amount (excl. VAT)
A05/03/2019 £                   9,500.52
B20/03/2019 £               119,380.00
C19/03/2019 £                   9,556.00
D06/03/2019 £                 91,860.00
D06/03/2019 £                 52,310.00
E11/03/2019 £                   6,500.00

 

Any help would be appreciated. Many thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Show value associated with a calculation in another column

Perhaps something along the lines of:

 

VAR __max = 

MAXX(
KEEPFILTERS(
VALUES('ForecaseAndActual'[Instructing Client])),CALCULATE(
SUM('ForecaseAndActual'[Order Amount (excl. VAT)]),USERELATIONSHIP('Date'[Date],ForecaseAndActual[Order Received]
)))

VAR __table = SUMMARIZE('ForecaseAndActual',[Instructing Client],"__orderAmount",CALCULATE(
SUM('ForecaseAndActual'[Order Amount (excl. VAT)]),USERELATIONSHIP('Date'[Date],ForecaseAndActual[Order Received]
)))

RETURN

MAXX(FILTER(__table,[__orderAmount]=__max),[Instructing Client])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


1 REPLY 1
Super User
Super User

Re: Show value associated with a calculation in another column

Perhaps something along the lines of:

 

VAR __max = 

MAXX(
KEEPFILTERS(
VALUES('ForecaseAndActual'[Instructing Client])),CALCULATE(
SUM('ForecaseAndActual'[Order Amount (excl. VAT)]),USERELATIONSHIP('Date'[Date],ForecaseAndActual[Order Received]
)))

VAR __table = SUMMARIZE('ForecaseAndActual',[Instructing Client],"__orderAmount",CALCULATE(
SUM('ForecaseAndActual'[Order Amount (excl. VAT)]),USERELATIONSHIP('Date'[Date],ForecaseAndActual[Order Received]
)))

RETURN

MAXX(FILTER(__table,[__orderAmount]=__max),[Instructing Client])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 232 members 2,534 guests
Please welcome our newest community members: