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

[Anonymous]

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 Number	Date	Location	Measure
1	1/1/2020	A
2	1/1/2020	A	50
1	1/10/2020	A
2	1/10/2020	A
3	1/10/2020	A
4	1/10/2020	A	50
1	1/1/2020	B
2	1/1/2020	B	50

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

[parry2k]

@Anonymous not fully clear , can you share sample data with expected output. 

[Anonymous]

Hi @parry2k  let me know if my edi thelped.  If not I'll try to clarify more.

[parry2k]

@Anonymous what output you expect, I got what you mean by sum on Jan 01st, and between Jan 01st to Jan 10th, now what?

[Anonymous]

@parry2k 

I want to take this and sum it.  So for Location A for the entire date range I would want to return 100 and Location B would return 50 in a card or however else I want to return the info.  Currently it is not letting me sum this even after I mark the column as whole number.

I also tried a switch statement

 SWITCH(True(),
    CALCULATE(MAX(Stops[StopNumber])
    ,GROUPBY(Stops[date],Stops[Location])) = MAX(Stops[StopNumber]), 1
    ,0)

Currently

[Anonymous]

@parry2kit doesn't seem to be recognizing this as a number or letting me change it to a number.

[parry2k]

@Anonymous I'm bit confused, what is not recognized as a number, I'm totally lost what you are trying to achieve. Can you put things in a excel sheet (raw data) and the expected output. You know your problem and what you are looking for, you need to explain like you are seeing it first time.

[Anonymous]

Hi @parry2k 

Hopefully this is better....

I have a table that has Stops and is by Date and Location.  I only want to grab the max stop number and flag it as a 1.  I will then take that 1 and multiply it by another value, lets just say 50 in this case.  I will then sum that 50 up to report the total # for the date range that is selected.  There are other fields in this table but these are the only ones that matter for this calculation.  

I do not see where I can load an excel spreadsheet to this but I will reload the spreadsheet I put above.

Date	StopNumber	Location	Measure
1/1/2020	1	a	50
1/1/2020	2	a	50
1/1/2020	3	a	50
1/1/2020	4	a	50
1/1/2020	5	a	50
1/1/2020	6	a	50
1/1/2020	7	a	50
1/1/2020	1	b	50
1/1/2020	2	b	50
1/1/2020	3	b	50
1/1/2020	4	b	50
1/1/2020	5	b	50
1/1/2020	6	b	50
1/2/2020	1	a	50
1/2/2020	2	a	50
1/2/2020	3	a	50
1/2/2020	4	a	50
1/3/2020	1	b	50
1/3/2020	2	b	50
1/4/2020	1	b	50

The attempts I have tried (which you've seen above) seem to work to a degree but they do not let me sum them.  So in my table it returns a 1 but a total sum of 0.  So above for the entire date range location a would have 100 while Location b would return a 150.

As you can see below where I'm just trying to flag what stop is the max stop I can get it to flag the stop but then I get a 0 as the sum.  If I remove stop numbers from the table it also drops to a 0.  I want to be able to use this in calculations without having to pull every stop # in but sum this up over whatever date range someone selects.

[v-deddai1-msft · 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])

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

[parry2k]

@Anonymous do you want this result:

[parry2k]

@Anonymous Here are the measures

Would appreciate Kudos 🙂 if my solution helped.

Sum = SUM ( Stops[Measure] )

Stop Number Flag = 
VAR __maxStopNumber =  
CALCULATE ( 
    MAX ( Stops[StopNumber] ), 
    ALLEXCEPT ( 
        Stops, 
        Stops[Location], 
        Stops[Date] 
    ) 
) 
RETURN 
( 
    __maxStopNumber = SELECTEDVALUE ( Stops[StopNumber] ) 
) + 0


Total by Stop Flag = SUMX ( Stops, [Stop Number Flag] * [Sum] )

drop location and Total by Stop Flag measure in the table visual or card or whatever visual and you will get the result.

[Anonymous]

@parry2k  yes I do