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
Anonymous
Not applicable

Dynamic sum of one value grouped by 2 other values plus a max

Hi all, sorry my subject is confusing but I'm trying to only flag or sum a value if it is a Max Stop Number grouped by a Location and a Date.  I want to return a value of 50 for each time this matches so I can sum up those 50's dynamically.  
Data Looks like this

Stop NumberDateLocationMeasure
11/1/2020A 
21/1/2020A50
11/10/2020A 
21/10/2020A 
31/10/2020A 
41/10/2020A50
11/1/2020B 
21/1/2020B50

 

So if someone summed this up above by Jan 1st, then Location A and B would have values of 50.  If they summed by Jan 1st through the 10th then Location A would have 100 while Locaiton B would have 50

 

I have tried the below but for whatever reason I can't sum the 1's that are returned or multiply it by anything to get what i want.

 

 

 

 

 Value(IF(
    CALCULATE(MAX(Stops[StopNumber]),ALLEXCEPT(Stops[date],Stops[Location]))
            =MAX(
Stops[StopNumber])          ,1          ,0))

 

 

 

 



1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Would you please create a measure to return values you want(50) for max stop number based on  date and location:

 

Measure =

VAR m =

    CALCULATE (

        MAX ( 'Table'[StopNumber] ),

        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Location] )

    )

VAR b = 50

RETURN

    IF ( m IN DISTINCT ( 'Table'[StopNumber] ), b, 0 )

 

SUM it based on location by the measure below:

 

SUMSTOPNUM = SUMX(ADDCOLUMNS('Table',"maxstopnum",[Measure]),[maxstopnum])

 

Untitled picture.png

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdK2LkA643VNhBGJI1uMawEBETvuLMMhyH8XD188oH76ng?e=ERsica

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

11 REPLIES 11

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.