Getting City with highest sum of hours per customer

[bagabo]

Hi there!

As in the subject, i'm currently searching for a formula to get the city with the highest sum of hours per customer.
So in the example, the customer has a variety of hours in the citys "Hamburg", "Kiel", "Rostock" and "Ruhr". But i want to find the City with the maximum sum of hours and write that into a new column for every customer.



So in this example, if he hast the most hours in "Hamburg", in every of the rows in the new column should be "Hamburg".
I tried a lot of things, but either it didn't work as planned or i get confusing errors like "circular dependency".

Thanks in advance!

[Ashish_Mathur]

Hi,

Why do you want to create a calculated column formula for this?  Why not a measure?

[v-yalanwu-msft · Accepted solution]

Hi, @bagabo ;

You could try create columns as follows:

RANK = 
RANKX ( 'Table',
        CALCULATE (
            SUM ( [Total Time and Work] ),
            ALLEXCEPT ( 'Table', 'Table'[WC Employee], 'Table'[Niederlassung] )),,ASC,DENSE)

highest =
CALCULATE (
    MAX ( [Niederlassung] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[WC Employee] ),
        [RANK]= CALCULATE ( MAX ( [RANK] ), ALLEXCEPT ( 'Table', 'Table'[WC Employee] ) )))

Another method is create a column or a measure.

Highest2 =
CALCULATE (
    MAX ( [Niederlassung] ),
    FILTER (ALLEXCEPT ( 'Table', 'Table'[WC Employee] ),
        CALCULATE (SUM ( [Total Time and Work] ),ALLEXCEPT ( 'Table', 'Table'[WC Employee], 'Table'[Niederlassung] ))
            = MAXX ( ALLEXCEPT ( 'Table', 'Table'[WC Employee] ),
                CALCULATE ( SUM ( [Total Time and Work] ),ALLEXCEPT ( 'Table', 'Table'[WC Employee], 'Table'[Niederlassung] ) ))))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bagabo]

Awesome! This works just fine! Thank you very much

[amitchandak]

@bagabo , Assume you have a measure for total hours as hours,

you can create a rank like this and filter that =1 in visual level filter

rankx(filter(allselected(Table[City], Table[Customer]), [Customer] = max([customer])), [hours],,desc,dense)

[bagabo]

Unfortunately, i cannot Filter this, cause I need also the data of the other cities, they should all summarize in the largest city.

I tried using 

LOOKUPVALUE(i3_Report[Niederlassung bereinigt];i3_Report[SUM p NL p CSE];MAX(i3_Report[SUM p NL p CSE]))

Where "SUM p NL p CSE" ist the sum of total hours per customer per city, but the problem is that it takes just the max value of this column, not the max value per customer.