Is there a way to do conditional average of average

[Bobiverse]

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

[richbenmintz]

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,

[richbenmintz]

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 I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[mhossain · Accepted solution]

@Bobiverse 

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.

[Greg_Deckler]

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.

You can add FILTERs