CODEWITHSUNDEEP
powerbidax
Chrismas007
Chrismas007

Reputation: 6105

Using Avg Measure When Specific Measure Unavailable

I have a situation where in 99% of a list of retail items, calculating Cost of Goods Sold is a simple measure:

COGS:= 
SUM([Units Sold]) * [Unit Purch Cost]

However, I have a few items that were not purchased during the time period, but were sold. Therefore, I'd like to use the average cost for an item in that subcategory:

Avg Special Cost :=
CALCULATE (
    SUM ( [Extended Cost] ) / SUM ( [Purchase Qty] ),
    Products[SubCategory] = "Special"
)

Now I'm trying to write the measure that would update the standard COGS measure:

COGS :=
CALCULATE (
    SUM ( [Units Sold] ) * [Unit Purch Cost],
    Products[SubCategory] <> "Special"
)
    + CALCULATE (
        SUM ( [Units Sold] ) * [Avg Special Cost],
        Products[SubCategory] = "Special"
    )

This seems to be working in the aggregate but I can't seem to get it to apply at the item level in a Power BI Matrix table. Any thoughts on possible modifications?

Upvotes: 0

Views: 31

Answers (1)

StelioK
StelioK

Reputation: 1781

Try this:

COGS :=
VAR AverageValue =
    CALCULATE ( DIVIDE ( SUM ( [Extended Cost] ), SUM ( [Purchase Qty] ) ) )
RETURN
    CALCULATE (
        SUMX ( 'Products', 'Products'[Units Sold] * 'Products'[Unit Puch Cost] ),
        Products[SubCategory] <> "Special"
    )
        + CALCULATE (
            SUMX ( 'Products', [Unit Sold] * AverageValue ),
            'Products'[SubCategory] = "Special"
        )

I am not sure if I got the tables right :) but I think this will get you at least headed in the right direction.

Upvotes: 1

Related Questions