cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Is there a way to do conditional average of average

Trying to achieve the below, but have not been successful in creating a combined DAX ...

Desired outcome:

Use filter in PowerBI to select product(s)

Then in table have products & a column with …

'Average sales rate for the latest 4weeks' =

Want to have the SUM of 'units' by 'location ID', for the filtered values (example filtered by products)

For the latest 4 weeks, which are based upon the latest date in the table, irrespective of filtering

Have that averaged out by 4 weeks

IF for a resulting 'location ID' there are no 'units', then show 0 value, for that specific 'location ID'

Then average the prior calculated 4 week average for all the 'location ID' where resulting unit values > 0

Example data table below

 Location ID Product Week Start Units Locations Selling 0001 Shampoo 6/29/2020 13 1 0003 Shampoo 6/29/2020 3 1 0004 Shampoo 6/29/2020 13 1 0006 Shampoo 6/29/2020 6 1 0007 Shampoo 6/29/2020 3 1 0008 Shampoo 6/29/2020 2 1 0009 Shampoo 6/29/2020 0 0 0011 Shampoo 6/29/2020 0 0 0014 Shampoo 6/29/2020 4 1 0001 Shampoo 7/6/2020 7 1 0003 Shampoo 7/6/2020 3 1 0004 Shampoo 7/6/2020 1 1 0006 Shampoo 7/6/2020 1 1 0007 Shampoo 7/6/2020 4 1 0008 Shampoo 7/6/2020 1 1 0001 Shampoo 7/13/2020 0 0 0003 Shampoo 7/13/2020 5 1 0004 Shampoo 7/13/2020 0 0

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Continued Contributor

## Re: Is there a way to do conditional average of average

Below should work if I understood your requirement correctly 🙂

Create below three measures--

1).  Units_measures = sum('Table'[Units])

2). Units_Sum at Location = CALCULATE(
SUMX(VALUES('Table'[Location ID]),[Units_measures])
,
DATESINPERIOD ( 'Table'[Week Start], LASTDATE ( 'Table'[Week Start] ), -28, DAY )
)

3).
Average sales rate for the latest 4weeks = CALCULATE(
AVERAGEX(VALUES('Table'[Location ID]),[Units_Sum at Location])
,
'Table'[Units]>0)

Please let me know if it helps.
4 REPLIES 4
Highlighted
Super User IX

## Re: Is there a way to do conditional average of average

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Continued Contributor

## Re: Is there a way to do conditional average of average

Below should work if I understood your requirement correctly 🙂

Create below three measures--

1).  Units_measures = sum('Table'[Units])

2). Units_Sum at Location = CALCULATE(
SUMX(VALUES('Table'[Location ID]),[Units_measures])
,
DATESINPERIOD ( 'Table'[Week Start], LASTDATE ( 'Table'[Week Start] ), -28, DAY )
)

3).
Average sales rate for the latest 4weeks = CALCULATE(
AVERAGEX(VALUES('Table'[Location ID]),[Units_Sum at Location])
,
'Table'[Units]>0)

Please let me know if it helps.
Highlighted
Solution Sage

## Re: Is there a way to do conditional average of average

Hi @Bobiverse,

Are you trying to compare the units sold of the selected product to the averge units sold for the product at the current location for the prior 4 weeks or across all locations for the prior 4 week?

Thanks,

Highlighted
Solution Sage

## Re: Is there a way to do conditional average of average

HI @Bobiverse,

Bit of a WAG but this measure should get you the Rolling 4 Week Avg based on what you have in your filter, and rows

``````Rolling 4 Weeks Sales Avg =
DIVIDE(
CALCULATE(
sum('Table'[Units]),
filter(ALL('Table'[Week Start]),'Table'[Week Start] in DATESINPERIOD('Table'[Week Start],  max('Table'[Week Start] ), -28, DAY ))
),
4
)``````

Hope this Helps a little,

Richard

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021