Non additive measure - advanced weighted average

[guruscz]

Hi all, 

I am building weighted average based on several conditions. Below is the measure which is calculationg the weighted average of power based on real generation of power plant which is in past till today basically the rest of rows is 0 for future. and price of hedge which is calculated for past and future. The formula is doing both and in rows in matrix it looks good the values and if I calculate it in excel it match. Problem is the TOTAL average in matrix which is giving incorect value. 

VAR hedge_volume = 
        SUMX(
            FILTER(DealGroupPWR, NOT DealGroupPWR[StrategyID] IN {1, 2, 3}),
            DealGroupPWR[GROUP_TotalVolumeMW])
VAR real_generation = 
        SUMX(
            FILTER(DealGroupPWR, DealGroupPWR[StrategyID] IN {1, 2, 3}),
            DealGroupPWR[_GenerationHedgeVolume])

VAR GeneHedgeVolume =
        SUMX(DealGroupPWR,
            DealGroupPWR[_GenerationHedgeVolume])
VAR diff = 
        (real_generation - hedge_volume)

VAR eur_hedge = 
            [EUR/MWh_onlyhedge] --measure of hedge price per mwh
VAR eur_realgen = 
            [EUR/MWh_onlyrealgen] --measure of real gen price per mwh


VAR calc_weight = IF(real_generation = 0, GeneHedgeVolume, real_generation)

RETURN   

        ((hedge_volume * eur_hedge) + (diff * eur_realgen)) / GeneHedgeVolume

[guruscz · Accepted solution]

In the end I fix the problem it was all coming from diff calculation. So I had to split the diff calculation like below

VAR hedge_volume = 
        SUMX(
            FILTER(DealGroupPWR, NOT DealGroupPWR[StrategyID] IN {1, 2, 3}),
            DealGroupPWR[GROUP_TotalVolumeMW])
VAR real_generation = 
        SUMX(
            FILTER(DealGroupPWR, DealGroupPWR[StrategyID] IN {1, 2, 3}),
            DealGroupPWR[GenerationHedgeVolume])

VAR diff = 
        IF(real_generation = 0, 0, real_generation - hedge_volume)
RETURN
    IF(HASONEVALUE('delivery date'[date]), diff, 
        SUMX(
            VALUES('delivery date'[date]),
            IF(
                CALCULATE(SUMX(FILTER(DealGroupPWR, DealGroupPWR[StrategyID] IN {1, 2, 3}), DealGroupPWR[GenerationHedgeVolume])) = 0,
                0, 
                -- real generation condition
                CALCULATE(SUMX(FILTER(DealGroupPWR, DealGroupPWR[StrategyID] IN {1, 2, 3}), DealGroupPWR[GenerationHedgeVolume])) - 
                -- real generation volume
                CALCULATE(SUMX(FILTER(DealGroupPWR, NOT DealGroupPWR[StrategyID] IN {1, 2, 3}), DealGroupPWR[GROUP_TotalVolumeMW]))
            )   -- hedge volume
        )
    )

[v-bmanikante]

Hi @guruscz,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-vpabbu]

Hi @guruscz,

Can you Please try the below measure:

adv_wt_avg =
VAR hedge_volume =
    SUMX(
        FILTER(DealGroupPWR, NOT DealGroupPWR[StrategyID] IN {1, 2, 3}),
        DealGroupPWR[GROUP_TotalVolumeMW]
    )



VAR real_generation =
    SUMX(
        FILTER(DealGroupPWR, DealGroupPWR[StrategyID] IN {1, 2, 3}),
        DealGroupPWR[_GenerationHedgeVolume]
    )



VAR GeneHedgeVolume =
    SUMX(DealGroupPWR, DealGroupPWR[_GenerationHedgeVolume])



VAR diff = (real_generation - hedge_volume)



VAR eur_hedge = [EUR/MWh_onlyhedge]  
VAR eur_realgen = [EUR/MWh_onlyrealgen]  



VAR calc_weight = IF(real_generation = 0, GeneHedgeVolume, real_generation)



-- Calculate weighted average per row
VAR WeightedAvg =
    DIVIDE(
        (hedge_volume * eur_hedge) + (diff * eur_realgen),
        GeneHedgeVolume
    )




VAR WeightedAvg_Total =
    DIVIDE(
        SUMX(
            ALLSELECTED(DealGroupPWR[Date]),  
            VAR hedge_volume_per_date =
                SUMX(
                    FILTER(DealGroupPWR, NOT DealGroupPWR[StrategyID] IN {1, 2, 3}),
                    DealGroupPWR[GROUP_TotalVolumeMW]
                )
            VAR real_generation_per_date =
                SUMX(
                    FILTER(DealGroupPWR, DealGroupPWR[StrategyID] IN {1, 2, 3}),
                    DealGroupPWR[_GenerationHedgeVolume]
                )
            VAR GeneHedgeVolume_per_date =
                SUMX(DealGroupPWR, DealGroupPWR[_GenerationHedgeVolume])
            VAR diff_per_date = real_generation_per_date - hedge_volume_per_date



            RETURN 
                (hedge_volume_per_date * eur_hedge) + (diff_per_date * eur_realgen)
        ),
        SUMX(ALLSELECTED(DealGroupPWR[Date]), GeneHedgeVolume), 
        0  
    )



RETURN
    IF(
        ISINSCOPE(DealGroupPWR[Date]),
        WeightedAvg,   
        WeightedAvg_Total  
    )

 Regards,

Vinay Pabbu

[techies]

Hi @guruscz please check this 

new = VAR hedge_volume =

        SUMX(

            FILTER(DealGroupPWR, NOT DealGroupPWR[StrategyID] IN {1, 2, 3}),

            DealGroupPWR[GROUP_TotalVolumeMW])

VAR real_generation =

        SUMX(

            FILTER(DealGroupPWR, DealGroupPWR[StrategyID] IN {1, 2, 3}),

            DealGroupPWR[_GenerationHedgeVolume])

VAR GeneHedgeVolume =

        SUMX(DealGroupPWR,

            DealGroupPWR[_GenerationHedgeVolume])

VAR diff =

        (real_generation - hedge_volume)

VAR eur_hedge =

            [EUR/MWh_onlyhedge] --measure of hedge price per mwh

VAR eur_realgen =

            [EUR/MWh_onlyrealgen] --measure of real gen price per mwh

VAR calc_weight = IF(real_generation = 0, GeneHedgeVolume, real_generation)

-- Handle total row separately

VAR WeightedAvg =

    ((hedge_volume * eur_hedge) + (diff * eur_realgen)) / GeneHedgeVolume

VAR WeightedAvg_Total =

    SUMX(

        VALUES(DealGroupPWR[Date]),  -- Iterates over dates to calculate row-wise

        ((hedge_volume * eur_hedge) + (diff * eur_realgen)) / GeneHedgeVolume

    )

RETURN

    IF(

        ISINSCOPE(DealGroupPWR[Date]),

        WeightedAvg,   

        WeightedAvg_Total  

    )

[bhanu_gautam]

@guruscz , Try using

VAR hedge_volume =
SUMX(
FILTER(DealGroupPWR, NOT DealGroupPWR[StrategyID] IN {1, 2, 3}),
DealGroupPWR[GROUP_TotalVolumeMW]
)
VAR real_generation =
SUMX(
FILTER(DealGroupPWR, DealGroupPWR[StrategyID] IN {1, 2, 3}),
DealGroupPWR[_GenerationHedgeVolume]
)
VAR GeneHedgeVolume =
SUMX(DealGroupPWR,
DealGroupPWR[_GenerationHedgeVolume]
)
VAR diff =
(real_generation - hedge_volume)

VAR eur_hedge =
[EUR/MWh_onlyhedge] --measure of hedge price per mwh
VAR eur_realgen =
[EUR/MWh_onlyrealgen] --measure of real gen price per mwh

VAR calc_weight = IF(real_generation = 0, GeneHedgeVolume, real_generation)

VAR row_weighted_avg =
SUMX(
DealGroupPWR,
((DealGroupPWR[GROUP_TotalVolumeMW] * [EUR/MWh_onlyhedge]) +
((DealGroupPWR[_GenerationHedgeVolume] - DealGroupPWR[GROUP_TotalVolumeMW]) * [EUR/MWh_onlyrealgen])) /
DealGroupPWR[_GenerationHedgeVolume]
)

RETURN
row_weighted_avg

[guruscz]

Hi Bhanu, unfortunetally that did not worked and I am getting Nan erro for all rows. In my original calculation I am getting in rows values aroun 100 eur per mwh, and then the total is 10...but should be around the 100 if I calculate it manually.