DAX Grouping by week and display over a daily x axis

[Anonymous]

HI all, I currently have a measure which is meant to show a 12 week average for each day of the week. A user would now like to be able to select multiple days of the week and for it to return a combined value for each day selected so therefore if a user selects Monday + Tuesday it would show the same value for both days which is the average of Monday + Tuesday over the last 12 weeks. My current formula looks like this:

var A = MAX(DIM_DATE[WEEK_DAY_NAME])
RETURN
CALCULATE(
'Calculated Measures'[AVG VISITS],
DATESINPERIOD(DIM_DATE[DATE],MAX(DIM_DATE[DATE])-2,-83,DAY), DIM_DATE[WEEK_DAY_NAME] = A
)

tbh i am not sure even where to start as my current formula is set up just to select a single day so i am not sure how i would go about having 

[v-xiaotang]

Hi @Anonymous 

Have you solved this question with the help of enthusiastic users? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaotang]

Hi @Anonymous 

Let's confirm your expected result again. 

Supposing your data are as follows, when you select Monday in slicer, the measure will return 53.5, right? Then the second question is, where do you want to put the measure in? In a card visual?

Then if you select Monday + Tuesday, it will return 52.304, right?

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Miguel,

I dont know why but it won't let me share upload xlsx/ pbix files, so here is an image of what I am trying to show:

What I am trying to display in the PBIX is a graph with a daily x axis for other values, but this value would need to be the sum of values divided by the number of days selected.

Please let me know if this is not helpful i will endeavour to work out how to upload a file.

[MFelix]

Hi @Anonymous ,

This depends on how you want to present your data:

• If you want to show the values on a card for example then you need to do a measure that uses a disconnect tabel with the weekdays and then have something similar to this:

Average = CALCULATE(AVERAGE('Table'[Value]),'Table'[weekday] in VALUES(WeekDays[Weekday]))

• If you want to fill the values on the table that are blank with those values then try the following measure:

AVeragewith 0 = IF (
    SUM ( 'Table'[Value] ) + 0 = 0,
    CALCULATE (
        AVERAGE ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[weekday] IN VALUES ( WeekDays[Weekday] ) )
    ),
    SUM ( 'Table'[Value] )
)

Results in attach PBIX file

[MFelix]

Hi @Anonymous,

You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[MFelix]

Hi @Anonymous 

Without any data is difficult to give you the correct answer can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.