Forum Discussion
Helper IMultiply 2 columns row then divide by count grouped
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.
Helper I
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 |
Community SupportHi 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.