Correct sales for opening times stores - DAX formula PowerBI

[Anonymous]

Hi all,

I'm pretty new to writing DAX-formulas and I'm stuck with solving the following issue. I've been searching for a long time for similar issues, but haven't found the solution that I'm looking for. Some help would be highly appreciated! Due to the sensitivity of my data I'm affraid I can't share my Powerbi file.

Problem: I would like to correct my monthly/yearly/etc. sales per article for the amount of days that stores have been opened. And I would like to be able to filter this for specific stores/countries or specific time periods (days, months, years, etc.). In other words, I'm looking for the (average) sales per month/.../year per store/area/country.

Relevant connected tables (simplified):

- Calendar

1. Date (linked with transaction data)
2. Year
3. Month
4. Week
5. Day

- Stores

1. Store_number (linked with transaction data)
2. Area
3. Country

- Transaction_data (row for every single transaction)

1. Transaction_number
2. Article_number
3. Turnover_incl_VAT
4. Sales_amount
5. Store_number (linked with stores)
6. Transaction_date (linked with calendar)

Condition: If the total salesamount (or turnover) > 0 for a certain day & store, that specific store has been opened on that specific day.

What I've tried uptill now:

Sales_activestorecorrection=SUM(Transaction_data[Salesamount])/Transaction_data[Openingdays_Store]

Openingdays_Store= ????

For Openingdays_Store I think I need to use a combination including COUNTROWS(), and the condition SUM(Sales_amount)>0.

Any help is highly appreciated!

[Anonymous]

After some more testing I found out that the suggested AverageX-formula above is not the solution. 

Example: Let's say that 'Product A' was sold 1x in 'Store 1', 2x in 'Store 2' and 0x in 'Store 3' in January 2019. In that case, the average sales per store for Product A in January should be (1+2+0)/3=1.

The suggested formula above doesn't include 'Store 3' because there was no transaction in that store during January 2019. Therefore it calculates the average as (1+2)/2=1,5. In reality Product A was actually available in Store 3 and therefore Store 3 should have been included in the formula.

[Anonymous]

If you only store amounts > 0 in your Transaction_data (why would you store 0's?), then AVERAGEX on days should be enough.

[Total Sales] = SUM ( Transaction_data[Sales_amount] )

[Daily Sales Average] =
averagex(
    'Calendar',
    [Total Sales]
)

This is because averagex omits BLANKS.

If, however, you do have 0's, you could do this:

[Daily Sales Average] =
averagex(
    'Calendar',
    calculate(
        [Total Sales],
        Transaction_data[Sales_amount] <> 0
    )
)

But this will not be as fast as the one above. Storing unnecessary data in fact tables is not recommended because it leads to slower DAX and a bigger model's memory consumption. Also, the more columns you have the worse the compression rate of the table.

Best
Darek

[Anonymous]

Thanks a lot for your help @Anonymous, exactly what I have been looking for! 

I only got the (first) formula working once I added CALCULATE and SUM before the sales amount (see below). On the other hand, for turnover it worked only without SUM. Pretty weird, can't explain the difference, but at least my results are correct. 

Average sales per period =

AVERAGEX(Calendar;CALCULATE(SUM('Transaction_Data'[Sales_amount])))

A related question. Now that I have my average sales per period, I also tried to make a column to show the amount of days each store has been opened for that period. I guess it involves quite a similar solution, but currently still puzzeling to get it right. Maybe something with Countx instead of averagex?