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
Metricbits
Frequent Visitor

Total of measure using showing MAX spend

Hi,

 

Please help me solve the issue with measure showing MAX spend over the last 16 weeks from the date selected in the slicer.

 

I have this measure and it works on line by line basis, but the total is wrong:

Max Spend last 16 weeks =
VAR MinDate =
    DATEADD ( 'OLAP Vel_Date'[End of Week], -112, DAY )
RETURN
    CALCULATE (
        MAX ( 'OLAP CUBE_VelocityCube_WB'[Consumption / Sale Amount] ),
        FILTER ( ALL ( 'OLAP Vel_Date' ), 'OLAP Vel_Date'[End of Week] >= MinDate )
    )

 

How should I add the total? I have tried different versions of summarizing, but nothing gave me the right result

Metricbits_0-1612264573592.png

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Metricbits , You need to change your return like

sumx(summarize(Table, Table[Row1], Table[Row2], "_1",RETURN
CALCULATE (
MAX ( 'OLAP CUBE_VelocityCube_WB'[Consumption / Sale Amount] ),
FILTER ( ALL ( 'OLAP Vel_Date' ), 'OLAP Vel_Date'[End of Week] >= MinDate )
)),[_1])

 

where row1 and row2 are the columns in the matrix row. Use the correct name

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Metricbits , You need to change your return like

sumx(summarize(Table, Table[Row1], Table[Row2], "_1",RETURN
CALCULATE (
MAX ( 'OLAP CUBE_VelocityCube_WB'[Consumption / Sale Amount] ),
FILTER ( ALL ( 'OLAP Vel_Date' ), 'OLAP Vel_Date'[End of Week] >= MinDate )
)),[_1])

 

where row1 and row2 are the columns in the matrix row. Use the correct name

Fantastic! This worked! Thank you very much!

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.