cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

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

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

2 ACCEPTED SOLUTIONS
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
7 REPLIES 7
Helper IV

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.

Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Helper IV

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

Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors