Forum Discussion

guruscz's avatar
guruscz
Frequent Visitor
1 year ago
Solved

Non additive measure - advanced weighted average

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's avatar
    guruscz
    1 year ago

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

     

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      guruscz
      Frequent Visitor
      1 year ago

      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. 

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    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  
        )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • guruscz's avatar
    guruscz
    Frequent Visitor
    1 year ago

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