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
qmartiny
Helper III
Helper III

MAXX of Measure not giving expected result

Hello,

 

Here's the short on the issue

I want to return the top category and value for this measure. However writing the dax for it gives me the wrong result.

 

qmartiny_0-1667303808274.png

 

 

I have the following measure calculating the difference between two snapshot values in a table as follows 

 

Total R4Q ACV Motion LW % = 
VAR CurrentPipe = CALCULATE (
    SUM ( 'CX Pipe Motion'[ACV EUR] ),
    REMOVEFILTERS( 'CX Pipe Motion'[Snapshot Short Desc]),'CX Pipe Motion'[Snapshot Short Desc] = "Today" && 'CX Pipe Motion'[DRM cont] in {"Active", "Booked/Won"} && 'CX Pipe Motion'[Closing Quarter] in {"2022-Q4", "2023-Q1", "2023-Q2", "2023-Q3"})

VAR LWPipe = CALCULATE (
SUM ( 'CX Pipe Motion'[ACV EUR] ),
REMOVEFILTERS( 'CX Pipe Motion'[Snapshot Short Desc]),'CX Pipe Motion'[Snapshot Short Desc] = "Today -7" && 'CX Pipe Motion'[DRM cont] in {"Active", "Booked/Won"} && 'CX Pipe Motion'[Closing Quarter] in {"2022-Q4", "2023-Q1", "2023-Q2", "2023-Q3"})
    
RETURN

((CurrentPipe - LWPipe) / LWPipe )

 

 

Now trying to get the MAX of this measure 

I've used this to get the max category but i'm getting the above result instead.

 

Max LW R4Q Solution % = 
MAXX(
    TOPN(1, 'CX Pipe Motion', [Total R4Q ACV Motion LW %], DESC),
    'CX Pipe Motion'[Solution Area L3]
)

 

 

Any ideas would be helpful.

Thanks a lot

 

 

Quentin

 

1 ACCEPTED SOLUTION

 

Can you please try below.

 

Max LW R4Q Solution % = 
MAXX(
    TOPN(1, VALUES('CX Pipe Motion'[Solution Area L3]), [Total R4Q ACV Motion LW %], DESC),
    'CX Pipe Motion'[Solution Area L3]
)

 







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

Proud to be a Super User!




View solution in original post

4 REPLIES 4
FarhanAhmed
Community Champion
Community Champion

Hi,

 

Can you please share a clear screenshot and have some sample data . It is very hard to read and understand.

 

 







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

Proud to be a Super User!




Hi Farhan,

 

Here is the table. I expect the result to display the top solution by % change.


It's showing me a different solution than expected

qmartiny_0-1667303738347.png

 

 

Can you please try below.

 

Max LW R4Q Solution % = 
MAXX(
    TOPN(1, VALUES('CX Pipe Motion'[Solution Area L3]), [Total R4Q ACV Motion LW %], DESC),
    'CX Pipe Motion'[Solution Area L3]
)

 







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

Proud to be a Super User!




Hi Farhan,

 

It's working if I change to ASC. Thank you!

 

 

Max LW R4Q Solution % = 
MAXX(
    TOPN(1, VALUES('CX Pipe Motion'[Solution Area L3]), [Total R4Q ACV Motion LW %], ASC),
    'CX Pipe Motion'[Solution Area L3]
)

 

 

 

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.