Average or Sum over already aggregated measure

[MaximilianR]

Hi all,

I am quite new to Power BI and I know that similar questions have been posted before and I read many answers but I am still not unterstanding how to achieve what I want.

Situation: I have successfully created measures for sales and sold quantity on a daily basis. I have also aggregated these values for for all items sold by one customer. The table is showing the article number (in fact unique numbers, just shortened for data protection) as well as values for the days the article was in stock (DayInSt) and for the full time period (all). In this case, a data slicer has been set between 01.07.2020 and 31.08.2020

(Article numbers on the left are unique, I just cut them to hide the full number)

This analysis works well for just one customer at a time but now I want to average and/or sum values for many customers. I know this might be slow for so many data points (220 customers * 250 Articles total * 365 days).

Here is the code I have used so far:

Sales Per Day (with blanks) = 
VAR CurrentDate = SELECTEDVALUE('DateTable'[Date])         

VAR NextSalesDate =
    CALCULATE(
        MIN('Sales Invoice Line'[Shipment Date]),           
        DATESBETWEEN(
            DateTable[Date],
            CurrentDate+1,
            CurrentDate+2000
        )
    )   

VAR PrevSalesDate =
    CALCULATE(
        MAX('Sales Invoice Line'[Shipment Date]),           
        DATESBETWEEN(
            DateTable[Date],
            CurrentDate-2000,
            CurrentDate
        )
    )               

VAR PrevAmount =
CALCULATE(    
    SUMX(
        FILTER('Sales Invoice Line',
            'Sales Invoice Line'[Shipment Date] = PrevSalesDate
        ),
        'Sales Invoice Line'[Amount]
    ),
    DATESBETWEEN(
        DateTable[Date],
        PrevSalesDate,
        CurrentDate
    )
)

VAR DaysSinceLastOrderTmp = DATEDIFF(PrevSalesDate, CurrentDate, DAY)
VAR DaysSinceLastOrder =
    IF(
        ISBLANK(DaysSinceLastOrderTmp),
        9999,
        DaysSinceLastOrderTmp
    )

VAR DaysBetweenOrders = DATEDIFF(PrevSalesDate, NextSalesDate, DAY)
VAR SalesDays =
    IF(
        DaysSinceLastOrder > 30, 
        BLANK(),
        IF(
            ISBLANK(DaysBetweenOrders),
            31,
            MIN(DaysBetweenOrders, 31)
        )
    )

VAR SalesPerDay =
    DIVIDE(
        PrevAmount,
        SalesDays,
        BLANK()
    )

RETURN SalesPerDay

The final measure is created with:

Av. Sales per Day (all) = 
    AVERAGEX('DateTable',
        [Sales Per Day (zeros)]
    )

Question: How do I summarize/average this over all customers? Mind that it needs to calculate the "Av. Sales per Day (all)" for an article for every customer separately and then sum or average it, enter it into the row and then go to the next article number doing the same.

Thanks in advance for the help!

Maximilian

[MaximilianR · Accepted solution]

Found a solution by myself:

Summed Av. Sales per Day per Item (all) = 
    SUMX(
        DISTINCT('Sales Invoice Line'[Cust. No]),
        [Av. Sales per Day (all)]
    )

It works, it is just very slow...

[MaximilianR]

Maybe, as an additional thing to show what I want, see below image: These are the sales and sold quantity values for ONE article and many customers in the rows. The measure I need would do the following steps:

1. Summarize or average these colums (The automatic summation at the bottom is obviously incorrect, when using excel to calculate I get a different result).
2. Do this for each article (see original post)