cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

COUNTA with filter

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)
2 REPLIES 2
Highlighted
Helper II
Helper II

Re: COUNTA with filter

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.

Highlighted
Frequent Visitor

Re: COUNTA with filter

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

 

 

 

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors