Multiply 2 columns row then divide by count grouped

[Sankzpower]

Hi guys

Hope you can help me out here. Sure, this is simple but again cannot execute cleanly on power bi. 

I have sample two tables both are connected via category. Each category has average use in sheet2. All I need is to calculate average use by multiplying the sleep into Avg use present in sheet 2 however its calculated by site. Sample power bi file here

The expected result is basically to find out total avg. use per site:

Site	Account	Category	Common Group	Sleeps	Avg.Use	Total Avg. Use
1A	1	C1E	E Group	125	1400	87500
1A	2	C1E	E Group	125	1400	87500
1A	3	C1G	G Group	125	1500	187500
1B	4	C1E	E Group	125	1400	175000
1B	5	C1E	G Group	125	1400	175000
1C	6	C1G	G Group	125	1500	187500

Thanks in advance

[v-juanli-msft]

Hi @Sankzpower 

Create a measure

Measure 2 =
DIVIDE (
    SUM ( Sheet1[Sleeps] )
        * SUM ( Sheet1[Avg.use] ),
    CALCULATE (
        COUNTROWS ( Sheet1 ),
        ALLEXCEPT (
            Sheet1,
            Sheet1[Common Group],
            Sheet1[Category],
            Sheet1[Site]
        )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sankzpower]

Hi @v-juanli-msft 

I wanted to mention you that the two tables are seperate but linked via category ID. There was an error in my data source which I previously attached. But power bi file has been updated to reflect current data here . I can confirm the result are the same as expected below.

Can you please check and let me know if  there is a workaround to achive the same result which i needed as below?

Thanks in advance

Site	Account	Category	Common Group	Sleeps	Avg.Use	Total Avg. Use
1A	1	C1E	E Group	125	1400	87500
1A	2	C1E	E Group	125	1400	87500
1A	3	C1G	G Group	125	1500	187500
1B	4	C1E	E Group	125	1400	175000
1B	5	C1E	G Group	125	1400	175000
1C	6	C1G	G Group	125	1500	187500

[v-juanli-msft · Accepted solution]

Hi @Sankzpower 

You data is not like the expected result picture.

Use measure below, result is

Measure 2 = DIVIDE(
    SUM ( Sheet1[Sleeps] )
        * SUM ( Sheet2[Avg.use] ),CALCULATE(DISTINCTCOUNT(Sheet1[Account]),ALLEXCEPT(Sheet1,Sheet1[Category],Sheet1[Site])))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.