cancel
Showing results for
Did you mean:
Highlighted 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 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
Highlighted 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() `

`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])`  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')` Highlighted 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() `

`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])`  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')` Announcements #### Community Blog

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

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

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries! #### 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
Users online (1,328)