Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

ID
1
2
2
2
2
2
2
2
2
2
2
FYgenerationROUNDTYPECON TYPESTRIKE
2019/200.0558InvoffshoreTran54
2020/212.79round 1onshoredis56
2021/221.37round 2nuclearTran58
2022/232.98round 3solardis60
2023/242.09HPCengerTran62
2024/251.52Invadvdis64
2025/263.97round 1offshoreTran66
2027/284.97round 2onshoredis68
2028/290.08round 3nuclearTran70
2029/300.9HPCsolarTran72
2030/310.06round 1energydis74

 

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

1 ACCEPTED SOLUTION

hi @Anonymous 

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

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

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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 ?

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

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

hi @Anonymous 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.