Unsure how to get the maximum for a group on the first date of the maximum

[Anonymous]

Hi there,

I have a graph that shows the total needed for all of our different licenses, the example being Nuke as a small subset of data.

The graph shows the correct total which is the sum of the usage on a week to week basis.  What I want to do at the bottom is show the week where the combined usage hit the maximum.

In the example here it would be 04/04/2022 and 55 being the total.  BUT when I remove the craft, I come up with 04/07/22 44, which is the maximum for just one of the lines.

Here is the measure I have crafted to try and do this (incorrectly as it seems).

LicenseExpiry = CALCULATE(MIN(CrewLicenseUsage[Date]),CrewLicenseUsage[LicenseTotal]=MAX('CrewLicenseUsage'[LicenseTotal]))

What do I need to change here to show the maximum number at the first week the maximum is hit for the combined items.

thanks

[Anonymous]

Hi @Anonymous ,

Could you please tell me that how to group?  Can you provide a simple PBIX file for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

I have tried another Measure,

Maximum License Count =

MAXX(

    KEEPFILTERS(VALUES(CrewLicenseUsage[Date])),

    CALCULATE(SUM('CrewLicenseUsage'[LicenseTotal]),'CrewLicenseUsage'[Location] IN {"Sydney","Vancouver"}))

Which I can come up with the max each week in the list, but then I can't figure how to ONLY show me the line with the Maximum.

If I remove the date then I get the correct max total, but now I am not sure what week that happens.

[amitchandak]

@Anonymous , if LicenseTotal is column then try like

LicenseExpiry =
var _max = MAXX(allselected('CrewLicenseUsage'),'CrewLicenseUsage'[LicenseTotal])
return
CALCULATE(MIN(CrewLicenseUsage[Date]),CrewLicenseUsage[LicenseTotal]=_max)

[Anonymous]

Thanks but that still shows me the single line of 04/07/22 with the total of 44, which is the single line of the software that has the highest amount.  
I am trying to show the week where the combined amount is at it's highest, which for the data is 04/04/22 and 55 being the total.

[amitchandak]

@Anonymous , For that You should use TOPN of Rank with Visual filter 1

Top 1 =calculate([measure], TOPN(1,allselected(Table[Week]),[measure],DESC), values(Table[Week]))

TOPN https://youtu.be/QIVEFp-QiOk

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[Anonymous]

I have this measure in place

License Top Week =

CALCULATE([TotalLicense], TOPN(1, all (CrewLicenseUsage[License]),[TotalLicense], DESC),VALUES(CrewLicenseUsage[License]))

The TotalLicense is just a sum of the LicenseTotal field, but I am still not seeing it limit to the week that has the first amount of highest value.

[Anonymous]

ok now I am confused, where is the Table[week] coming from?