Creating impact calculator

[filarap]

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

[filarap]

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

[Anonymous]

Hi @filarap,

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_T

Regards,
Xiaoxin Sheng

[filarap]

Hi Xiaoxin Sheng,

This does provide an idea where is the biggest impact, but what i need is to calculate exactly how many seconds that impact is.

This would help me to understand a level of potential improvement.

Thank you for your help

Filarap

[Anonymous]

HI @filarap,

>>what i need is to calculate exactly how many seconds that impact is

I'm not so sure for this, can you please explain more about this?(e.g expected result)

Regards,
Xiaoxin Sheng

[filarap]

Certainly , ill be happy to.

Please have a look at example already provided and formula that goes into impact column for each row.

=($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

The principle is that we are taking a specific user, process (Row 2 example) and calculating the difference between total weighted average results and the same, but without observed process or user. 

In the example above, we can see weighted average of 262 seconds (C9) and in E2 we can see how much seconds would this result change, if we remove Name 1 from the total calculation. This is giving us the impact of this user/process.

I hope this makes it clear.

Regards

[dnmrk]

Hi - Any luck in solving this? Trying to make the same sort of measure.

Thanks.

[filarap]

Hi @dnmrk 

Please see below.

Result and freq are measures I made.

Name is column in table.

Impact =
CALCULATE
([Result],ALLSELECTED(Table1[Name))
-
divide((CALCULATE([Result]*[Freq],ALLSELECTED(Table1[Name]))
    -
    CALCULATE([Result]*[Freq])),
        CALCULATE([Freq],ALLSELECTED(Table1[Name]))-[Freq])

Hope this helps

Filarap

[Anonymous]

Hi @filarap,

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