Dynamic Weighted Average in measure

[PKPK90]

Hi, I am trying to create a dynamic measure (dynamic because I want to change date range) to calculate Weighted average per SiteItem. Below you can find example of calculations and expected final result. 

I was able to create Total per SiteItem and Percent Of GrandTotal per SiteItem, but I do not know how to create weighted average that can be dynamicly change by selected date range.

GT = CALCULATE(

    SUM('PurchaseOrders'[ExtendedCost]),

    ALLEXCEPT('PurchaseOrders',PurchaseOrders[Site],PurchaseOrders[Item])

    )

% of GrantTotal =

VAR TotalPerSiteItem_ =

CALCULATE(

    SUM('PurchaseOrders'[ExtendedCost]),

    ALLEXCEPT('PurchaseOrders',PurchaseOrders[Site],PurchaseOrders[Item])

    )

return

DIVIDE(SUM(PurchaseOrders[ExtendedCost]),TotalPerSiteItem_)

[PKPK90]

I was able to create DAX that gives me WeightedItemCost per line. I need to create final table with SUM of [WeightedItemCost] per Site_Item_Key. Any thoughts?

EVALUATE
VAR tabl1 =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( PurchaseOrders, PurchaseOrders[Site_Item_Key] ),
            "ItemExtendedCost", [M_ExtendedCost]
        ),
        ALLSELECTED ( Items )
    )
 VAR tabl2 =
    SELECTCOLUMNS (
        PurchaseOrders,
        PurchaseOrders[Site_Item_Key],
        PurchaseOrders[PurchaseOrder],
        PurchaseOrders[PurchaseOrderLine],
        PurchaseOrders[ItemCost],
        PurchaseOrders[ExtendedCost]
    )
VAR tabl3 =
    NATURALINNERJOIN ( tabl1, tabl2 )
 VAR tabl4 =
    ADDCOLUMNS (
        tabl3,
        "%ofTotal", FORMAT ( DIVIDE ( [ExtendedCost], [ItemExtendedCost] ), "Percent" ),
        "WeightedItemCost",
            ROUND (
                DIVIDE ( [ExtendedCost], [ItemExtendedCost] ) * PurchaseOrders[ItemCost],
                2
            )
    )
   RETURN
    tabl4

[Anonymous]

Hi @PKPK90 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[PKPK90]

Hi @Anonymous do you have any idea about that issue?

Thanks in advance!

[PKPK90]

Here you can find it https://drive.google.com/file/d/1LTCnTpkCPpmpvIF9jiXVOrb961oxnK4s/view?usp=sharing

I have created a WeightedAverageCost table, but instead of table I want to have measure that will show TotalWeightedItemCost with posibility to change date range from PurchaseOrder table.

[Anonymous]

HI @PKPK90,

I'd like to suggest you use ALLSELECTED function to replace the ALLEXCEPT to interact with the filter effect from raw table relationship and filters:

GT =
CALCULATE (
    SUM ( 'PurchaseOrders'[ExtendedCost] ),
    ALLSELECTED ( 'PurchaseOrders' ),
    VALUES ( PurchaseOrders[Site] ),
    VALUES ( PurchaseOrders[Item] )
)

% of GrantTotal =
VAR TotalPerSiteItem_ =
    CALCULATE (
        SUM ( 'PurchaseOrders'[ExtendedCost] ),
        ALLSELECTED ( 'PurchaseOrders' ),
        VALUES ( PurchaseOrders[Site] ),
        VALUES ( PurchaseOrders[Item] )
    )
RETURN
    DIVIDE ( SUM ( PurchaseOrders[ExtendedCost] ), TotalPerSiteItem_ )

Regardrs,

Xiaoxin Sheng

[PKPK90]

Hi, thank you for your reply, 

Forgive my misunderstanding, but I still don't know how to solve the final calculation problem with weighted item cost..