Average sales by weekday not working

[Mega82]

Hi everyone, I'm trying to summarize the data as an average per day of the week and by product (see graph) but at the moment I can only do this with the totals of a given period (in the graph I have the data of one full month).

The actual result I'm looking for is to have the same graph but with the average sales by day of the week of a given period (defined by a slicer). The problem is that when I put Average in in the Value... the numbers are really low compare to the actual numbers that should show up.

I think I will have to group the data with a Calculate function or a group by but I don't seem to find the correct way of doing so.

Can anyone help me with this? ... I attached the table so you guys can see the type of data I'm using.

Thanks in advance for your help.

[OwenAuger · Accepted solution]

@Mega82

If I understand you correctly, you are wanting to show the average sum of Gals per day, grouped by weekday/product (as per your column chart).

For example if there were four Fridays in the selected period, where the sum of DIESEL Gals on each of those Fridays was 4000, 5000, 6000 and 7,000, you would expect to see Friday/DIESEL column = (4,000+5,000+6,000+7,000)/4 = 5,500.

A measure that would achieve this is:

Average of Daily Gals =
AVERAGEX (
    VALUES ( Recons_Sales_Details[Fecha] ),
    CALCULATE ( SUM ( Recons_Sales_Details[Gals] ) )
)

(change the comma to a semicolon in the above code if needed 🙂 )

Try placing that measure in the visual instead of Gals.

Please post back if that works or if you were looking for something different.

Regards,

Owen

[Mega82]

Thanks a lot for your response Owen. Your approach is working perfectly!!