Forum Discussion

Eth's avatar
Eth
New Member
4 years ago
Solved

sumx with hierarchy

Sorry if this has been asked already but haven't been able to find anything.    I have a table that looks like this: Region Branch Item Qty Requested Qty Sent Date Region A Branch 1 P...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You're grouping items together before dividing. Try calculating the ratio for each item and then averaging.

    Fulfillment Rate =
VAR Summary =
    ADDCOLUMNS (
        SUMMARIZE (
            Daily,
            Daily[Item],
            "@Sent", SUM ( Daily[Qty Sent] ),
            "@Requested", SUM ( Daily[Qty Requested] )
        ),
        "@Ratio", MIN ( 1, DIVIDE ( [@Requested], [@Sent] ) )
    )
RETURN
    AVERAGEX ( Summary, [@Ratio] )

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

You're grouping items together before dividing. Try calculating the ratio for each item and then averaging.

Fulfillment Rate =
VAR Summary =
    ADDCOLUMNS (
        SUMMARIZE (
            Daily,
            Daily[Item],
            "@Sent", SUM ( Daily[Qty Sent] ),
            "@Requested", SUM ( Daily[Qty Requested] )
        ),
        "@Ratio", MIN ( 1, DIVIDE ( [@Requested], [@Sent] ) )
    )
RETURN
    AVERAGEX ( Summary, [@Ratio] )

 

Eth's avatar
Eth
New Member
4 years ago

Works great, thanks!