cancel
Showing results for
Search instead for
Did you mean:
Highlighted
aetedford Frequent Visitor

## Calculate AVG Cost Per Unit BY QTR

What I am trying to do is to create an additional column in a matrix visualization that looks at a UNIT ID and gets the AVG COST of that UNIT for a specific QTR by looking in the PRICING table and getting the AVG COST during that period. (A unit may have a price change in that qtr, multiple price changes, or a previous qtr change).

I have been going through different formulas and nothing gives me what I am expecting.

Here are my tables and the current MATRIX.

Any assistance would be appreciated on how to create a measure on the AVG COST PRICE.

Thanks

I have 3 tables:

a) PRODUCT NAMES b) SALES c) COST CHANGES I have pulled a MATRIX VISUALIZATION together that correctly shows QTY UNITS SOLD, TOTAL \$ SOLD and a measure using SUMX showing AVG SALE PER UNIT by YEAR and QTR. Seems to work Perfectly. 1 ACCEPTED SOLUTION

Accepted Solutions
MarkLaf Member

## Re: Calculate AVG Cost Per Unit BY QTR

I think I've come up with a solution, but it involves leveraging some helper tables. I'd be interested if there is a more straightforward way to do it... Anyway:

Create (i.e. New Table button in Modeling ribbon) a CALENDAR table:

`CALENDAR = CALENDARAUTO() `

Add a custom column:

`QTRKEY = [Date].[Year]&[Date].[QuarterNo]`

Create similar column in SALES:

`QTRKEY = [YEAR]&[QTR]`

Create a QTRBRIDGE table to do a many2many connection with SALES:

`QTRBRIDGE = SUMMARIZE('CALENDAR','CALENDAR'[QTRKEY])`

Add relationships:  Create a measure that calculates the average product price over CALENDAR based off COST CHANGES:

```AVGCOSTcal =
AVERAGEX('CALENDAR',
CALCULATE(AVERAGE('COST CHANGES'[UNIT COST]),
FILTER('COST CHANGES',
'COST CHANGES'[PRICE CHANGE] =
CALCULATE(
MAX('COST CHANGES'[PRICE CHANGE]),
FILTER(
ALLEXCEPT('COST CHANGES','COST CHANGES'[UNIT ID]),
'CALENDAR'[Date]>='COST CHANGES'[PRICE CHANGE]
)
)
)
)
)```

Create a measure that you can throw on the SALES MATRIX to show the average price of your products by QTR:

`AVGCOST = CALCULATE([AVGCOSTcal],'QTRBRIDGE','SALES')` 1 REPLY 1
MarkLaf Member

## Re: Calculate AVG Cost Per Unit BY QTR

I think I've come up with a solution, but it involves leveraging some helper tables. I'd be interested if there is a more straightforward way to do it... Anyway:

Create (i.e. New Table button in Modeling ribbon) a CALENDAR table:

`CALENDAR = CALENDARAUTO() `

Add a custom column:

`QTRKEY = [Date].[Year]&[Date].[QuarterNo]`

Create similar column in SALES:

`QTRKEY = [YEAR]&[QTR]`

Create a QTRBRIDGE table to do a many2many connection with SALES:

`QTRBRIDGE = SUMMARIZE('CALENDAR','CALENDAR'[QTRKEY])`

Add relationships:  Create a measure that calculates the average product price over CALENDAR based off COST CHANGES:

```AVGCOSTcal =
AVERAGEX('CALENDAR',
CALCULATE(AVERAGE('COST CHANGES'[UNIT COST]),
FILTER('COST CHANGES',
'COST CHANGES'[PRICE CHANGE] =
CALCULATE(
MAX('COST CHANGES'[PRICE CHANGE]),
FILTER(
ALLEXCEPT('COST CHANGES','COST CHANGES'[UNIT ID]),
'CALENDAR'[Date]>='COST CHANGES'[PRICE CHANGE]
)
)
)
)
)```

Create a measure that you can throw on the SALES MATRIX to show the average price of your products by QTR:

`AVGCOST = CALCULATE([AVGCOSTcal],'QTRBRIDGE','SALES')` 