Skip to main content
cancel
Showing results for 
Search instead 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

Reply
menphis21
Helper IV
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
Mikelytics
Resident Rockstar
Resident Rockstar

HI @menphis21 

 

Please try thw followng:

 

sample table called (SampleAverage)

Mikelytics_2-1673971820017.png

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

 

Mikelytics_1-1673971804455.png

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.

 

 

------------------------------------------------------------------
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!

View solution in original post

@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])
)

Mikelytics_3-1673971959743.png

 

second column:

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

Mikelytics_4-1673972030964.png

 

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.

 

 

 

 

------------------------------------------------------------------
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!

View solution in original post

7 REPLIES 7
menphis21
Helper IV
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.

 

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.

------------------------------------------------------------------
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!

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.

 

------------------------------------------------------------------
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!
Mikelytics
Resident Rockstar
Resident Rockstar

HI @menphis21 

 

Please try thw followng:

 

sample table called (SampleAverage)

Mikelytics_2-1673971820017.png

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

 

Mikelytics_1-1673971804455.png

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.

 

 

------------------------------------------------------------------
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!
Mikelytics
Resident Rockstar
Resident Rockstar

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.

------------------------------------------------------------------
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!

Hi @Mikelytics ,

 

Thank you. Below you can find an example

manager DEpartementAging Days
AvyA34
DanielA40
GinetteB36
LuciaB43

 

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 

@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])
)

Mikelytics_3-1673971959743.png

 

second column:

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

Mikelytics_4-1673972030964.png

 

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.

 

 

 

 

------------------------------------------------------------------
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!

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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