The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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
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
@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