Forum Discussion

11leven's avatar
11leven
New Member
7 years ago

Filtering on AverageX and Calculate

Hello every one,

Appreciate any help in to insights of row context and filter context.

 

I'm calculating Avg entrances of restaurents(buildings) by using a working measure below.

 

Avg Measure DayOfWeek =
AVERAGEX(
  KEEPFILTERS(VALUES('DATE_D'[WeekOfYear])),
       CALCULATE( SUM('ENTRY_F'[Direction])

          ))

 

 

All good with the above formula. 

 

The situation is, all available buildings should be included EXCEPT one building for Avg calculations.

 

i.e dayofweek is "Friday" AND building name is "XYZBuilding" AND serving_type is "Lunch". --Do not include

 

All other serving types like breakfast and dinner should be included for this building too.

 

I came up with this(though it does not work!) as all the tables are related in model.

 

Avg Measure DayOfWeek =
AVERAGEX(
KEEPFILTERS(VALUES('DATE_D'[WeekOfYear])),
CALCULATE( SUM('ENTRY_F'[Direction]),
(FILTER(WC_DATE_D,(VALUES(DATE_D[DayOfWeek])<>"Friday"))
&& (FILTER('BUILDING_D',(VALUES('BUILDING_D'[Name]<>"XYZBuilding")

&& (FILTER('BUILDING_D',(VALUES('BUILDING_D'[Service_Type]<>"Lunch"))
))))

 

Thanks again for your help.

 

Raki

Need Help

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Raki,

     

    Try this formula, please. If it doesn't work, please provide a sample file. You can upload it to the cloud drive like OneDrive, GoogleDrive, then share the download link here. Please mask the sensitive parts first.

     

    Avg Measure DayOfWeek =
CALCULATE (
    AVERAGEX (
        KEEPFILTERS ( VALUES ( 'DATE_D'[WeekOfYear] ) ),
        SUM ( 'ENTRY_F'[Direction] )
    ),
    FILTER ( WC_DATE_D, DATE_D[DayOfWeek] <> "Friday" ),
    FILTER (
        'BUILDING_D',
        'BUILDING_D'[Name] <> "XYZBuilding"
            && 'BUILDING_D'[Service_Type] <> "Lunch"
    )
)

     

    Best Regards,
    Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    hi can any one help me how to calcualte average and and same value we need to show for all rows in table if slicer changes needs to change the value