DAX measure

[Anonymous]

Hi everyone! could you please help me with the scenario below. Appreciated your help on this in advance.

I have territories sales data. There are territories where 2 or 3 managers are responsible at the same time.

I need to calculate sales for each manager but if one of these territories has multiple managers I need to divide sales on this territory by amount of managers.

Once again, great thanks!

[amitchandak]

@Anonymous , You can divide by distinctcount(Table[Manager])

or calculate(distinctcount(Table[Manager]), allexcept(Table, Table[territories]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Great thanks for your help! Sure! It's just an example. I' ve put column Terr_count, to understand how often do we meet this territory.

Terr_count	Territories	Manager	Sales
3	Западный округ	Райков Олег	45733
3	Карасунский округ	Райков Олег	163207
3	Прикубанский округ	Райков Олег	100224
3	Центральный округ	Райков Олег	127229
3	Западный округ	Плохая Елена	188585
3	Карасунский округ	Плохая Елена	24962
3	Прикубанский округ	Плохая Елена	70243
3	Центральный округ	Плохая Елена	84036
3	Западный округ	Дудзенко Виталий	31748
3	Карасунский округ	Дудзенко Виталий	120382
3	Прикубанский округ	Дудзенко Виталий	188838
3	Центральный округ	Дудзенко Виталий	25871
1	Динская	Дудзенко Виталий	165085
1	Восточный район	Дудзенко Виталий	109245
1	Приморский район	Дудзенко Виталий	51807
1	Центральный район	Дудзенко Виталий	184341
1	Белореченск	Плохая Елена	187547
1	Полтавская	Плохая Елена	70056
1	Славянск-на-Кубани	Плохая Елена	89100
1	Майкоп	Плохая Елена	40496
1	Эстосадок	Райков Олег	8153
1	Дагомыс	Райков Олег	148516
1	Лазаревское	Райков Олег	11920
1	Сочи	Райков Олег	154375

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

You can try this expression to get the result:

Measure =
SUMX (
    SUMMARIZE (
        'Table',
        [Territories],
        [Terr_count],
        "sales", SUM ( 'Table'[Sales] ) / [Terr_count]
    ),
    [sales]
)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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