topic Re: Calculate Average based on tow categories from different table in DAX Commands and Tips

Calculate Average based on tow categories from different table

SalmanABDul_97 — Sun, 20 Mar 2022 11:50:28 GMT

hi everyone!!

i have 3 tables Sles table , City Table ,Company table.

i need dax function that calculate avg for every Sales in each company by each City.

final table

NEW YORK | TEXSAS | Miami

34555 | 555555| 566777

The values are the sum of all companies from each city

Re: Calculate Average based on tow categories from different table

Whitewater100 — Sun, 20 Mar 2022 13:18:39 GMT

Hi:

If you have any sample data the answer could be better. For now can you try:

Avg Company Sales by city =
AVERAGEX(CityTable,
AVERAGEX(VALUES(CompanyTable[Name]),
CALCULATE(AVERAGE(SalesTable[Sales Amount]))
)
)

Re: Calculate Average based on tow categories from different table

SalmanABDul_97 — Sun, 20 Mar 2022 13:38:41 GMT

salesamount is a calculated measure. sorry i did not mention that. i will add dummy data to help you.

thanks

so it will be like that :

Avg Company Sales by city =
AVERAGEX(CityTable,
AVERAGEX(VALUES(CompanyTable[Name]),
CALCULATE(AVERAGE(SalesTable, [Sales Amount]))
)
)

Re: Calculate Average based on tow categories from different table

Whitewater100 — Sun, 20 Mar 2022 13:45:29 GMT

OK. Thank you. If you have Sales Measure already [Sales Amount] it can be:

Avg Company Sales by city =
AVERAGEX(CityTable,
AVERAGEX(VALUES(CompanyTable[Name]),
CALCULATE(AVERAGE([Sales Amount]))
)
)

If you include a file I can double check it.

Re: Calculate Average based on tow categories from different table

SalmanABDul_97 — Sun, 20 Mar 2022 14:13:09 GMT

I accepted your answer as a solution. Thanks !!

if it is okay for you i have used it in another dax function

to calculate STD in the same way but it not working , it is gives me 0 for all

STD =
STDEVX.P(CityTable,
STDEVX.P(VALUES(CompanyTable[Name]),
CALCULATE(STDEVX.P(SalesTable, [Sales Amount]))
)
)

i really need it for std, i will be greatful.

Re: Calculate Average based on tow categories from different table

Whitewater100 — Sun, 20 Mar 2022 16:27:33 GMT

Hi:

Thank you. If you have standard deviation as a separate measure you can try:

Avg Company STD by city =
AVERAGEX(CityTable,
AVERAGEX(VALUES(CompanyTable[Name]),
CALCULATE(AVERAGE([STD]))
)

Re: Calculate Average based on tow categories from different table

SalmanABDul_97 — Sun, 20 Mar 2022 19:33:47 GMT

Thank you for the time and effort you put toward helping me find the solution.

I meant calculating the standard deviation by city for every company in it. Same the first dax you did but for STD is not give any right number, it just give me zeros

Re: Calculate Average based on tow categories from different table

Whitewater100 — Sun, 20 Mar 2022 20:12:17 GMT

Hi:

I'm sorry about that. Do you have any sample data I could look at and what types of figures you expect for the solution for STD?

Thanks!

Re: Calculate Average based on tow categories from different table

SalmanABDul_97 — Sun, 20 Mar 2022 20:24:48 GMT

thanks. your solution it work now. after creating std measure.

but i wonder, why we use avreage here ? its givs corecct answer, but why we need to use avg?

Avg Company STD by city =
AVERAGEX(CityTable,
AVERAGEX(VALUES(CompanyTable[Name]),
CALCULATE(AVERAGE([STD]))
)

Re: Calculate Average based on tow categories from different table

Whitewater100 — Mon, 21 Mar 2022 00:25:49 GMT

Hi:

Can you see the last line of code, it has [STD} to base the average from.

CALCULATE(AVERAGE([STD]))
)

Re: Calculate Average based on tow categories from different table

SalmanABDul_97 — Tue, 22 Mar 2022 03:00:39 GMT

sorry i did it again and seems i have problem

i want it to calculate the avg in this way

let assume that new york has 4 company

and the sales are 444, 2344, 5555, 5553 for each company in new york.

the averge sholud be 3474.

i dont think the dax function do it like that, right?

really sorry. but its important for me and i have been working on it for days.