COUNTA with filter

[wojcikgp]

Hello all, I'm new here, thanks in advance for your help.

I have data in table like below

DateName | WeekDay | Location | [Sales] | [Counta]

2020-01-01 | monday | locationA | 100 | 5 

2020-01-08 | monday | locationA | 10 | 5

2020-01-15 | monday | locationA | 100 | 5

2020-01-22 | monday | locationA | 10 | 5

2020-01-29 | monday | locationA | 0 | 5

for measure [Counta] I need count of how many times there was a sale in certain location in certain weekday (monday) (for all dates)

right now:

[Counta] = CALCULATE(COUNTA(DimDate[Weekday]);ALL(DimDate[DateName]))

and its 5 and it should be 4.

how can I enter the "[Sales] >0" condition?

CALCULATE(COUNTA(DimDate[WeekDay]);FILTER(ALL(DimDate[DateName]);[Sales]>0))

doesnt work properly, it shows all dates in months for location A to have sale (29), I want only mondays (4)

[baghdadi62]

Hi,

use this measure:

Measure_1 = CALCULATE(COUNTROWS(MyTable), MyTable[Sales]>0)

Or:

Measure_2 = CALCULATE(COUNTROWS(MyTable), MyTable[WeekDay]="monday", MyTable[Location]="locationA", MyTable[Sales]>0)

you don't have to use all for all dates in Filter.

[wojcikgp]

Hi,

thank You 4 your answer. But probably I expained it wrong.

source data set has DimDate table (Date and Weekday), DimLocations table (Location) and FactSales table (with columns [Sales], [DateID], [LocationID])

As table, I meant visualisation table, which has values Sales, and it is grouped by 

Date | WeekDay | Location

I need to calculate difference between sum of sales for each row in visualisation table, and average for weekday and location in this row, for all dates in data set, but only for these that had sales >0

if there wasnt last condition, measure 

Measure1 = CALCULATE(COUNTA(DimDate[CalendarDayName]);ALL(DimDate[DateName]))

is all right, but it returns for each row all weekdays in DimDate, so if filtered is one month, then 4 or 5.

so visualisation table is

DateName | WeekDay | Location | Sales(Sum) | Measure1 | AverageSales | Difference SumSales vs AverageSales