How to calculate a benchmark and compare values according to the benchmark

[menphis21]

Hello,

I have data on invoices and their payments distributed by manager and each manager is attached to a department.
I would like to compare, for example, the payment period of the manager (depending on the selection) in relation to his attached department.

This involves calculating the average paiement time for each department.

How can we do ?

Thank you,

Avy

[Mikelytics · Accepted solution]

HI @menphis21 

Please try thw followng:

sample table called (SampleAverage)

Measures:

01 Person Value = AVERAGE(SampleAverage[Aging Days])

02 Department Average = 
CALCULATE(
    AVERAGEX(
        VALUES(SampleAverage[manager ]),
        AVERAGE(SampleAverage[Aging Days])
    ),
    ALLEXCEPT(SampleAverage,SampleAverage[DEpartement])
)

03 Benchmark = [01 Person Value] - [02 Department Average]

Result

this is using measures and based on your data granularity it could look a little bit different. for that i need you data model.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[menphis21]

HI @Mikelytics ,

Thank you, it works.

Could you please explain what you did ?

With the allexpect you a not considering the departement, i would do the inverse, like an "over partition by" in SQL departement to calculate the needed average.

[Mikelytics]

Hi @menphis21,

small addon. Exactly it is similar to partition. for each row, when the measure is calculated it ignores all filter except the existing department filter which means that for calculating the average a partition of all rows of the department which is stated in the current row is provided as baseline for the average calculation.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Mikelytics]

Hi @menphis21 

Great and sure, please find below an explenation

So lets take a look on this formula since this is the most important one.

02 Department Average = 
CALCULATE(
    AVERAGEX(
        VALUES(SampleAverage[manager ]),
        AVERAGE(SampleAverage[Aging Days])
    ),
    ALLEXCEPT(SampleAverage,SampleAverage[DEpartement])
)

You said you want to have the average performance of a manager in a department. This means I need to take all managers, identify their base value and then take the average of it. In general this is done by the following part:

AVERAGEX(
   VALUES(SampleAverage[manager ]),
   AVERAGE(SampleAverage[Aging Days])
)

So the VALUES function says: give me the list of the managers.

AVERAGEX says that please iterate through the list of managers (VALUES), calculate a function (here I chose AVERAGE) and after iterating through all manager execute the AVERAGE of each iteration result. It might be a little bit confusing here that I have the blue and the orange average function. So if each manager has only one line-iitem then you could replace AVERAGE(SampleAverage[Aging Days]) by SampleAverage[Aging Days] and it should still work. I only used AVERAGE to also get a value in case you have redundant values.

so this was the first part. now comes the surrounding function because you still have the filter context and based on this filter context each calculation would iterate only through one line-item which is the line-item itself. But I want the function to iterate through all line-items of the deparment this is why I said ALL on the table EXCEPT the department because I want to consider this filter in my calculation. In result when the function starts to run it iterates through all line-items which have the same department like the row of the function itself.

I hope this helps a little bit. 🙂

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Mikelytics · Accepted solution]

HI @menphis21 

Please try thw followng:

sample table called (SampleAverage)

Measures:

01 Person Value = AVERAGE(SampleAverage[Aging Days])

02 Department Average = 
CALCULATE(
    AVERAGEX(
        VALUES(SampleAverage[manager ]),
        AVERAGE(SampleAverage[Aging Days])
    ),
    ALLEXCEPT(SampleAverage,SampleAverage[DEpartement])
)

03 Benchmark = [01 Person Value] - [02 Department Average]

Result

this is using measures and based on your data granularity it could look a little bit different. for that i need you data model.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Mikelytics]

Hi @menphis21 

Can you please provide sample data with expected outcome?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[menphis21]

Hi @Mikelytics ,

Thank you. Below you can find an example

manager	DEpartement	Aging Days
Avy	A	34
Daniel	A	40
Ginette	B	36
Lucia	B	43

The Average Aging days for departement A is 37 and B 39.5.

The aim is to display a kpis when i will selected the corresponding manager, for example, Avy is -3 days below the aging average days of the departement  (37)

Hope that its more clear 

[Mikelytics · Accepted solution]

@menphis21 

Normally I try to avoid calculated columns but it might be a good option for you in the case. Please find below a version with calculated columns:

first column:

CC Department Average = 
CALCULATE(
    AVERAGEX(
        VALUES(SampleAverage[manager ]),
        AVERAGE(SampleAverage[Aging Days])
    ),
    ALLEXCEPT(SampleAverage,SampleAverage[DEpartement])
)

second column:

CC Benchmark = [Aging Days] - [CC Department Average]

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.