Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @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
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 @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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |