Calculate Average based on tow categories from different table

[SalmanABDul_97]

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

[Whitewater100 · Accepted solution]

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]))
)
)

[Whitewater100 · Accepted solution]

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]))
)
)

[SalmanABDul_97 · Accepted solution]

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]))
)
)

[Whitewater100]

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. 

[SalmanABDul_97]

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.

[Whitewater100]

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]))
)

[SalmanABDul_97]

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 

[Whitewater100]

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!

[SalmanABDul_97]

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]))
)

[Whitewater100]

Hi:

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

 CALCULATE(AVERAGE([STD]))
)

[SalmanABDul_97]

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.