Greetings, and lets see if I can do this correctly with the graphics.
The DAX code which is a typical (as they say) weighted average template follows this text.
The table that contains the columns; [PRV] & [PRI QTY] to be multiplied and produce the product [Asset Value], follow the DAX code.
At each row, I want to divide the [Asset Value] by the Sum of the [PRI QTY], not the individual values at the roes themselves.
As you can see from the column [WAvg], I am getting the same result as [PRV]; why, because I am not retaining the "TotalGSF" from the VAR in the DAX code.
I thought by using the VAR, the "TotalGSF" would be retained as a constant for the DIVIDE function, but not true, its obvious the row value of [PRI QTY] is being used.
How do I retain the "TotalGSG" for use in the DIVIDE function? Thank you.
____________________________________________________________________________
WAvg:=VAR TotalGSF = [Total Pri Qty]
RETURN
DIVIDE (
SUMX (
VALUES ( GFEBS_IDS_ALLSITES[BUSINESS_ENTITY] ),
CALCULATE(SUM(GFEBS_IDS_ALLSITES[PRV]) * SUM(GFEBS_IDS_ALLSITES[PRI QTY]),
FILTER(GFEBS_IDS_ALLSITES,
AND(
GFEBS_IDS_ALLSITES[BUSINESS_ENTITY] = "24015",
GFEBS_IDS_ALLSITES[PRIUM] = "SF"
)
)
)
),
TotalGSF
)
Total Pri Qty:=CALCULATE(
SUM(GFEBS_IDS_ALLSITES[PRI_QTY]),
FILTER(GFEBS_IDS_ALLSITES,
AND(
GFEBS_IDS_ALLSITES[PRIUM] = "SF",
GFEBS_IDS_ALLSITES[BUSINESS_ENTITY] = "24015")
)
//the measure that populates the VAR TotalGSF
Total Pri Qty:=CALCULATE(
SUM(GFEBS_IDS_ALLSITES[PRI_QTY]),
FILTER(GFEBS_IDS_ALLSITES,
AND(
GFEBS_IDS_ALLSITES[PRIUM] = "SF",
GFEBS_IDS_ALLSITES[BUSINESS_ENTITY] = "24015")
)
)
__________________________________________________________
PRV PRI QTY Asset Value WAvg
1,626,962 5,825 9,477,053,650 1,626,962
962,771 3,447 3,318,671,637 962,771
962,771 3,447 3,318,671,637 962,771
962,771 3,447 3,318,671,637 962,771
962,771 3,447 3,318,671,637 962,771
7,691 64 492,224 7,691
1,106,614 3,962 4,384,404,668 1,106,614
Bookmarks