cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Resolver IV
Resolver IV

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

View solution in original post

1 REPLY 1
Highlighted
Resolver IV
Resolver IV

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

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors