How to find the minimum of average column based on another column.

[Anonymous]

Hi. I have 2 columns here. Column 'Name' and column 'Sales'.

I would like to find the minimum of average Sales based on each type of Name. And I want to return the name ( because I have 2 visual cards. One showing the value, another one showing the name of minimum average sales) 

[AlB · Accepted solution]

@Anonymous 

NameM V2 =
MAXX (
    TOPN ( 1, DISTINCT ( Table1[Name] ), CALCULATE ( SUM ( Table1[Sales] ) ), ASC ),
    [Name]
)

SalesM V2 =
MINX ( DISTINCT ( Table1[Name] ), CALCULATE ( SUM ( Table1[Sales] ) ) )

And I just reread and see that you need the average? If so use AVERAGE instead of of SUM

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

Not unless I see a proper data sample or pbix clearly showing what the issue is

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hey @AlB  This is my link to the pbix file. I also show the bar chart. The answer should show the lowest value on the card where for the year 2020, the Name is supposed to be Irong 7, surname PAC R with the value of 347.70 

https://1drv.ms/u/s!AoKh3nlpMpSOgl7cRjhFOqfXmMv8?e=mWghoq

Really appreciate it if you could help me with this.

[AlB]

@Anonymous 

I don't understand. Those values are exactly what your measures are showing in the attached file. What is the problem?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB I should be getting PAC R. But I'm getting PAC N instead. Am I wrong anywhere?

[AlB · Accepted solution]

@Anonymous 

You need to consider name and surname together.  Otherwise you are grouping and calculating the average by name only or by surname only. I guess that is not what you need.

Best SurName V2 =
MAXX (
    TOPN (
        1,
        SUMMARIZE ( Sheet1, Sheet1[SURNAME], Sheet1[NAME] ),
        CALCULATE ( AVERAGE ( Sheet1[SALE] ) ), ASC
    ),
    [SURNAME]
)

Best Name V2 =
MAXX (
    TOPN (
        1,
        SUMMARIZE ( Sheet1, Sheet1[SURNAME], Sheet1[NAME] ),
        CALCULATE ( AVERAGE ( Sheet1[SALE] ) ), ASC
    ),
    [NAME]
)

Best Sales V2 =
MINX (
    SUMMARIZE ( Sheet1, Sheet1[SURNAME], Sheet1[NAME] ),
    CALCULATE ( AVERAGE ( Sheet1[SALE] ) )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

YES! Thank you so much!

[AlB · Accepted solution]

@Anonymous 

NameM V2 =
MAXX (
    TOPN ( 1, DISTINCT ( Table1[Name] ), CALCULATE ( SUM ( Table1[Sales] ) ), ASC ),
    [Name]
)

SalesM V2 =
MINX ( DISTINCT ( Table1[Name] ), CALCULATE ( SUM ( Table1[Sales] ) ) )

And I just reread and see that you need the average? If so use AVERAGE instead of of SUM

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thanks for replying! I was actually needed this formula to find for SURNAME too 

NameM V2 =
MAXX (
    TOPN ( 1, DISTINCT ( Table1[SurName] ), CALCULATE ( SUM ( Table1[Sales] ) ), ASC ),
    [SurName]
)

and when I create this measure, It does not show the correct value. And I was wondering why. But if I replace ASC with DESC (just to check the opposite value for the lowest, it shows the correct max average for that surname)

Do you have any other way to solve this?

[AlB]

Hi @Anonymous 

NameM =
MAXX (
    TOPN ( 1, DISTINCT ( Table1[Name] ), CALCULATE ( SUM ( Table1[Sales] ) ) ),
    [Name]
)

SalesM =
MAXX ( DISTINCT ( Table1[Name] ), CALCULATE ( SUM ( Table1[Sales] ) ) )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

That formula works perfectly. But it was showing the highest value. I want the lowest value. Which part should I change?

I change MAXX to MINX but it only works for the Sales. The Name doesn't change, it still showing the largest