Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KrisT
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
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.