cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aetedford Frequent Visitor
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

 

 

PRODUCTS.png

 

 

 

 

 

b) SALES

 

SALES.png

 

 

 

 

 

 

 

 

 

 

 

 

c) COST CHANGES

 

PRICE CHANGE.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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. 

 

MATRIX.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
MarkLaf Member
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:

sales.PNG

cal.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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')

fin.PNG

1 REPLY 1
MarkLaf Member
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:

sales.PNG

cal.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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')

fin.PNG