Forum Discussion
Creating impact calculator
Hi all,
I am new with Power BI and i am trying to re-create on tool i used in Excel. It basically tells me what is an impact of one person/process to the team result. Below is table and function showing excel example.
=((testimp[[#Totals],[ttls]]-[@ttls])/(testimp[[#Totals],[Freq]]-[@Freq]))-testimp[[#Totals],[Result]]
Name Freq Result ttls Impact
| name 1 | 10 | 300 | 3000 | -3.06131 |
| name 2 | 15 | 257 | 3855 | 0.679559 |
| name 3 | 7 | 652 | 4564 | -21.6475 |
| name 4 | 50 | 217 | 10850 | 27.31679 |
| name 5 | 4 | 364 | 1456 | -3.15207 |
| name 6 | 24 | 150 | 3600 | 24.7367 |
| name 7 | 23 | 329 | 7567 | -13.9368 |
133 262 34892
I have tried several options using Sumx, allselected and some filtering. Also tried creating totals measures and using it against, but nothing works for me.
I would appreciate any help or direction.
Thank you
8 Replies
Hi Anonymous,
I can't find explain about testimp function which you used in formula, can you please provide more detail about this?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Apologies, this was a table reference
=($D$9-$D2)/($B$9-$B2))-$C$9
A B C D E 1 Name Freq Result ttls Impact 2 name 1 10 300 3000 -3.06131 3 name 2 15 257 3855 0.679559 4 name 3 7 652 4564 -21.6475 5 name 4 50 217 10850 27.31679 6 name 5 4 364 1456 -3.15207 7 name 6 24 150 3600 24.7367 8 name 7 23 329 7567 -13.9368 9 133 262 34892 Hope this clarifies
regards
Hi Anonymous,
You can try to use follow measure if it suitable for your requirement:
Measure = VAR freq_T = CALCULATE ( SUM ( Test[Freq] ), ALLSELECTED ( Test ) ) VAR result_T = CALCULATE ( SUM ( Test[Result] ), ALLSELECTED ( Test ) ) VAR tts_T = CALCULATE ( SUM ( Test[ttls] ), ALLSELECTED ( Test ) ) RETURN ( tts_T - MAX ( Test[ttls] ) ) / ( freq_T - MAX ( Test[Freq] ) ) - result_TRegards,
Xiaoxin Sheng