Weighted Average in a Hierarchy

[reh169]

So here is my issue. I have invoices, and because I am using line level data I am counting them distinctly. I have company level, a region level and a yard level. I want to know the weighted average that it takes to get the invoices exported at all three levels.  So I need the whole total number of invoices to be used in company calculation and I am not sure how to do this. For example

Company A 150 invoices,  5 days to export

Company B 300 invoices,  9 days to export

Company C 500 invoices, 15 days to export

Company D 50 Invoices, 3 days to export

Total 1000 Invoices

At the company level the weighted average would for Company A would be (150*5)/1000

Then at a region level the weighted average would be based on the 150 and not the 1000

Then at the yard level the weighted average would be based on the number for the region.

What logic do I need to make this work?

[v-shex-msft]

Hi @reh169,

I am not sure how you define “region level”, so I create a sample data table below and calculate the weighted average on

different levels. If it doesn’t meet your requirement, kindly share your sample data and expected the result to me.

Table:

Measures: 

Company level = SUM('Table'[Invoices])*SUM('Table'[Days to export])/CALCULATE(SUM('Table'[Invoices]),ALLSELECTED('Table'))

Region level = SUM('Table'[Invoices])*SUM('Table'[Days to export])/CALCULATE(SUM('Table'[Invoices]),FILTER(ALLSELECTED('Table'),'Table'[Region]=SELECTEDVALUE('Table'[Region])))

Yard level = SUM('Table'[Invoices])*SUM('Table'[Days to export])/COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Region]=SELECTEDVALUE('Table'[Region])))

Result:

Best Regards,

Jack Chen

[reh169]

Hi Jack that did not work for me.  I think the issue is the duplicated lines are not accounted for. As you can see from the data I have lots of invoices with the same number column D. I only want to count each invoice once and the column that has the number of days till it was exported is column T. Yards are cities that make a up a region and several regions will make up a company if that helps.

[v-shex-msft]

HI @reh169 ,

You can try to use the following measure formulas if they suitable for your requirement:

AVG for Company= 
VAR icCount =
    CALCULATE (
        COUNTROWS ( VALUES ( Test[IC] ) ),
        ALLSELECTED ( Test ),
        VALUES ( Test[CompanyCode] )
    )
VAR exported =
    CALCULATE (
        AVERAGE ( Test[End Work to Exported Invoice] ),
        ALLSELECTED ( Test ),
        VALUES ( Test[CompanyCode] )
    )
RETURN
    icCount * exported
        / CALCULATE (
            COUNTROWS ( VALUES ( Test[IC] ) ),
            ALLSELECTED ( Test )
        )

AVG for Region= 
VAR icCount =
    CALCULATE (
        COUNTROWS ( VALUES ( Test[IC] ) ),
        ALLSELECTED ( Test ),
        VALUES ( Test[CompanyCode] ),
        VALUES ( Test[Region] )
    )
VAR exported =
    CALCULATE (
        AVERAGE ( Test[End Work to Exported Invoice] ),
        ALLSELECTED ( Test ),
        VALUES ( Test[CompanyCode] ),
        VALUES ( Test[Region] )
    )
RETURN
    icCount * exported
        / CALCULATE (
            COUNTROWS ( VALUES ( Test[IC] ) ),
            ALLSELECTED ( Test ),
            VALUES ( Test[CompanyCode] )
        )

AVG for Yard =
VAR icCount =
    CALCULATE (
        COUNTROWS ( VALUES ( Test[IC] ) ),
        ALLSELECTED ( Test ),
        VALUES ( Test[CompanyCode] ),
        VALUES ( Test[Region] ),
        VALUES ( Test[YardName] )
    )
VAR exported =
    CALCULATE (
        AVERAGE ( Test[End Work to Exported Invoice] ),
        ALLSELECTED ( Test ),
        VALUES ( Test[CompanyCode] ),
        VALUES ( Test[Region] ),
        VALUES ( Test[YardName] )
    )
RETURN
    icCount * exported
        / CALCULATE (
            COUNTROWS ( VALUES ( Test[IC] ) ),
            ALLSELECTED ( Test ),
            VALUES ( Test[CompanyCode] ),
            VALUES ( Test[Region] )
        )

Regards,

Xiaoxin Sheng