Inventory Quantity Based On Another Column Value

[timmyvl23]

Hi All,

I have a data set, what I'm trying to do is based on the count of the value in the 'Shipment Reference' column, divide 'Inventory Quantity' by the count of 'Shipment Reference' . For instance, if there is two counts of the same 'Shipment Reference' value, divide 'Inventory Quantity' by the count. 

Highlighted below are two examples where there are two counts of of the same 'Shipment Reference' and 'Inventory Quantity' is divided by the count. The result is 'Inventory Quantity 3'. However, when it is aggregated, the rows of data are not summed. Its Total Inventory Quantity / Total Count of Shipment Reference.

I want the rows to be as shown but to sum up to the values that are listed in the rows, not 1,418.12. Total should be much greater. How would I do this?

This is the measure for 'Inventory 3':

Inventory Quantity 3 =
VAR MeasureX =
CALCULATE(COUNTA('Shipment Leg Cost Headers'[Shipment Reference]),ALLEXCEPT('Shipment Leg Cost Headers','Shipment Leg Cost Headers'[Shipment Reference]))
VAR MeasureY =
SUM('Item Freight Data'[Inventory Quantity])
RETURN
IF(MeasureX=1,MeasureY,MeasureY/MeasureX)

[v-rongtiep-msft · Accepted solution]

Hi @timmyvl23 ,

Please create another measure based on Inventory Quantity 3 .

measure =
VAR _b =
    SUMMARIZE (
        'Item Freight Data',
        'Item Freight Data'[shipment reference],
        "aaa", [inventory Quantity 3]
    )
RETURN
    IF (
        HASONEVALUE ( 'Item Freight Data'[shipment reference] ),
        [inventory Quantity 3],
        SUMX ( _b, [aaa] )
    )

If it still does not help, please provide more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@timmyvl23,

Try this measure:

Inventory Quantity 3 Total =
SUMX (
    VALUES ( 'Shipment Leg Cost Headers'[Shipment Reference] ),
    [Inventory Quantity 3]
)