Devide sum of a column by sum of another column if a column contains

[moeconsult]

ID
1
2
2
2
2
2
2
2
2
2
2

FY	generation	ROUND	TYPE	CON TYPE	STRIKE
2019/20	0.0558	Inv	offshore	Tran	54
2020/21	2.79	round 1	onshore	dis	56
2021/22	1.37	round 2	nuclear	Tran	58
2022/23	2.98	round 3	solar	dis	60
2023/24	2.09	HPC	enger	Tran	62
2024/25	1.52	Inv	adv	dis	64
2025/26	3.97	round 1	offshore	Tran	66
2027/28	4.97	round 2	onshore	dis	68
2028/29	0.08	round 3	nuclear	Tran	70
2029/30	0.9	HPC	solar	Tran	72
2030/31	0.06	round 1	energy	dis	74

What am trying to archieve is to calculate weighted for each round so I can dispalay this on a line chat.

Formular : Sum(generation)*Sum(strike)/sum(generation) but I want to calculate this for each Round . i.e Round 1, Round 2 etc .

Please help

[amitchandak]

I think the correct formula should be

Sum(generation * strike)/sum(generation)

You can try in the following way

Create a new column prod = generation * strike

Weighted Avg = calculate(divide(sum(prod),sum(generation))

Or 
Weighted Avg = calculate(divide(sumx(table,generation*strike),sum(generation))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[moeconsult]

Thanks for swift response, but I want to calculate individual weight average for each round 1 , Round 2, Round 3 etc in the round column . I dont know if that makes sense ?

[amitchandak]

You can use a slicer or can use row in the matrix, visual level filter etc

Or have filter in the calculation

Weighted Avg = calculate(divide(sum(prod),sum(generation),round= "round1")

or
Weighted Avg = calculate(divide(sum(prod),sum(generation),filter(table,round= "round1"))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[moeconsult]

I have tried this  Calculate(divide(sum('valuation Generation'[Prod]),Sum(('valuation Generation'[generation]),filter(('valuation Generation',[Allocation Round] = "Allocation Round 1"))

I got this error message: The expression refers to multiple columns . Multiple columns cant not be converted into scalar value .

further clarification would be appreciated .

Thanks for your help

[v-lili6-msft · Accepted solution]

hi @moeconsult 

Please adjust the formula as below:

CALCULATE (
    DIVIDE (SUM ( 'valuation Generation'[Prod] ),SUM ( 'valuation Generation'[generation] )),
        FILTER ( 'valuation Generation', 'valuation Generation'[ROUND] = "Round 1" )
)

If not your case, please share your expected output based on above sample data.

Regards,

Lin

[moeconsult]

@v-lili6-msft  thanks for your help with this formula but some of the measures created for each allocation rounds are coming up with blank in the the chart when when i click show data, its showing blanks. any suggestion to fix this would help