How to write DAX formula for several criteria of filters?

[milkynight]

Hi all,

Im having difficulty to write DAX formula to accommodate both situations below:

- Situation 1: Filter by channel, Avg Daily Sales = MTD Sales / Actual Selling Days (by each channel)

- Situation 2: No filter, Avg Daily Sales = MTD Sales (Total) / Actual Selling Days (by Main Channel)

Existing formula, works for Situation 1 but not for Situation 2

Actual ADS = 
sum(MTD Sales)/
max(Actual selling days)

Sample data:

Channel	Actual selling days	MTD Sales	Avg daily sales
Main Channel	8	100	13
Sub channel 1	9	50	6
Sub channel 2	5	50	10

Total MTD Sales = 200

Avg Daily Sales = 200/ 8 = 25

Thanks much for your help!

[milkynight]

Thanks @Greg_Deckler , tried ISFILTERED and it worked 🙂

[milkynight]

@Greg_Deckler Thanks, I haven't known about these 2 before. Let me try to see if it works!

[Greg_Deckler · Accepted solution]

@milkynight Perhaps use ISFILTERED or HASONEVALUE?