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
Highlighted
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
Highlighted
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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 66 members 1,267 guests
Please welcome our newest community members: