How to get an Weighted Average

[janssenfrank]

I have a table with three colums: year, target and value. So that lookes like this:

YEAR	TARGET	VALUE
2022	Profit	5%
2023	Profit	6%
2024	Profit	7%
2022	Scrap	10%
2023	Scrap	9%
2024	Scrap	8%

I want to know the Average Value if the Target is "Profit".

I need that value (Average Value ) to be shown in a card-visual. 

For example: In my report I select date range December 1st 2023 to December 1st 2024

That is 31 days of 365 in 2023 and 335 of 366 days in 2024.

So, the Average Value should be:

(31/365) * 6% + 355/366) * 7% = 6,92%.

It is like a Weighted Average. But how can I get this one value?

[Anonymous · Accepted solution]

Hi All,
Firstly  Greg_Deckler thank you for your solution!
And @janssenfrank ,I'm glad to tell you that your needs can be achieved, the following is the implementation of some ideas for your reference, we first selectvalue to get the filter to select the largest and smallest date, and then determine whether their year is a leap year, and then calculate the number of specific days, you can complete your needs, in accordance with your needs should be +335 so the results will be a little off!

Measure = 
VAR StartDate=SELECTEDVALUE('Date Table'[Date],MIN('Date Table'[Date]))
VAR EndDate=SELECTEDVALUE('Date Table'[Date],MAX('Date Table'[Date]))
VAR IsLeapYearA=IF(MOD(YEAR(StartDate),4)=0&&(MOD(YEAR(StartDate),100)<>0||MOD(YEAR(StartDate),400)=0),
    366,
    365)
VAR IsLeapYearB=IF(MOD(YEAR(EndDate),4)=0&&(MOD(YEAR(EndDate),100)<>0||MOD(YEAR(EndDate),400)=0),
366,
365)
VAR DaysInStartYear = DATEDIFF(StartDate, DATE(YEAR(StartDate), 12, 31), DAY) + 1
VAR DaysInEndYear = DATEDIFF(DATE(YEAR(EndDate), 1, 1), EndDate, DAY)
VAR WeightedStartYear = CALCULATE(MAX('Table'[VALUE]), 'Table'[YEAR] = YEAR(StartDate), 'Table'[TARGET] = "Profit") * (DaysInStartYear / IsLeapYearA)

RETURN
(WeightedStartYear+(DaysInEndYear/IsLeapYearB))*CALCULATE(MAX('Table'[VALUE]),'Table'[YEAR]=YEAR(EndDate),'Table'[TARGET]="Profit")

I would be honoured if my solution solves your problem!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@janssenfrank See if this gets you there: Better Weighted Average per Category - Microsoft Fabric Community