Forum Discussion

powerlight1's avatar
powerlight1
Icon for Helper I rankHelper I
1 year ago
Solved

Help with Filtering Using A Separate Table

Hi, I need help with filtering using a separate table and calculating an average based on filtered records.   I have a Sales table Date, Sales 1/11/2024, 1 2/11/2024, 2 3/11/2024, 3 4/11/2024,...
Need Help
  • Shahid12523's avatar
    Shahid12523
    1 year ago

    Your slicer table isn’t filtering Sales, so the average uses all 6 days.

     

    Fix: Create a measure with TREATAS

     

    Avg Sales by Slicer =
    CALCULATE(
    AVERAGE(Sales[Sales]),
    TREATAS(VALUES(slicer_date[Date]), Sales[Date])
    )


    Now the slicer dates will filter Sales, and the average for 1–5 Nov will return 3.

     

    Alternative: just relate slicer_date[Date] → Sales[Date] directly.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi powerlight1 

What does the measure do? It has an incorrect syntax.

 

That aside and even if you use an implicit measure and assuming that there's only one row for each date, the average would still have returned correctly even without the date colum  had there been a relationship betweeen your sales and slicer tables.

 

 

If there are more than one rows for each date, you will need to create a measure to calculate the daily average

AVERAGEX ( ALLSELECTED ( slicer_date ), CALCULATE ( SUM ( sales[amount] ) ) )

 

 

powerlight1's avatar
powerlight1
Icon for Helper I rankHelper I
1 year ago

danextian 

the filter measure should be:

Filter_measure = IF( MAX(SALES[DATE]) = ALLSELECTED(slicer_date[date]),1,0).