cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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])

I have book! Learn Power BI from Packt


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])

I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 293 members 2,984 guests
Please welcome our newest community members: