Count Columns that don't contain zero

[android1]

Hi,

Trying to count the number of weeks which have been invoiced.

In attached image, Week numbers are at the top (Wk 3 - 21).

The values (14.00, 17.50 etc are the column [Visits_NoHrs])

Trying this fromula ->

Num Invoiced Weeks = CALCULATE(COUNTA(vw_PivotVisitsInvoiced[Visits_NoHrs]),vw_PivotVisitsInvoiced[Visits_NoHrs] <> 0) but getting 1 for the example in attached. (expect 😎

Regards,

Gerry

[vanessafvg]

@android1

wouldn't > 0 work better then <> 0?   i am assuming its a number?

your example doesn't make sense to me, what should be 1 or shouldn't be?

[android1]

Hi vanessafvg,

>0 returns the same. Num Invoiced weeks should be 8 as there are 8 times (weeks) where [Visits_NoHrs] is not zero.

I want to count the weeks where [Visits_NoHrs] <> 0.

I get 1 rather than 8.

 

[vanessafvg]

@android1

what does your data look like? can you post a screenshot?

what if you tried something liek this 

Num Invoiced Weeks = CALCULATE(countrows(vw_PivotVisitsInvoiced), not(isblank(vw_PivotVisitsInvoiced[Visits_NoHrs]))

is the data blank or 0?

[android1]

Hi,

Data is blank. 

Result using your formula returning the same as mine.

[vanessafvg]

@android1

what is your data type on no of hours?   is the row you posted below an example of what your date looks like in the tables at a detailed level?

 what about, noofrows = calculate(countrows(vw_PivotVisitsInvoiced), vw_PivotVisitsInvoiced[Visits_NoHrs] > 0)

you need to provide more information otherrwise it will be very difficult to provide you with help based on the information  you have given.  It seems to me either the data type is not as expecvted or i am nto seeing the actually data as it is in the table. as a detail level.

[android1]

[Visits_NoHrs] is Decimal Number, Format: General

I have the correct invoiced hrs for each week.

Some weeks have blank Invoiced Hrs & some have value. So what I'm trying to do is count the weeks that 

have a value ie invoiced hrs not equal to blank.

[vanessafvg]

@android1  what i am asking you though is [Visits_NoHrs] a measure  you created or is it in your base data

maybe something like this would work

calculate (countrows(table), filter (table,  sum([Visits_NoHrs]) > 0))

[android1]

Sorry, it's a column (not a calculated created by me) It came staright from the Database.

No luck with that either (Num Invoiced Weeks = calculate (countrows(vw_PivotVisitsInvoiced), filter (vw_PivotVisitsInvoiced,  sum([Visits_NoHrs]) > 0)))

[vanessafvg]

@android1

can you not post a picture of your data in the table?  i.e a screenshot of the actual table (not the results in a visual) of vw_PivotVisitsInvoiced

[android1]

Yeah but there are loads of columns in the DB.

[android1]

Maybe I'll state exactly what 'm trying to do.

New employees start working at any week of the year.

E.g

I have a worker who starts in week 10. They generate [Visits_NoHrs] (column directly from DB) from week 10 onwards.

Say they are off in Week 15 (so no [Visits_NoHrs] generated in Week 15). Back in Wk 16

& work Wk 17, 18, 19 ,20. In Total they have hours in 10 weeks.  I want to count these weeks.

So below I would have 8 as they generated [Visits_NoHrs] in 8 weeks.

I have this calculated column - Week = WEEKNUM([Visits_Date],21) which returns the Week of the year.

[vanessafvg]

@android1

thanks for the info.  you should be able to slice the data any which way so even with the week num, your measure should work.

i have created a similar scenario and this definitely works.  what are you getting with this?    There is obviously something else going on if the below doesn't work.

I am working on the premise here that 

vw_PivotVisitsInvoiced[Visits_NoHrs] is a decimal

all rows = countrows(vw_PivotVisitsInvoiced)   what do you get back with this?

the measure as previously stated should work when you do this

no of rows  with hours= calculate(countrows(vw_PivotVisitsInvoiced), vw_PivotVisitsInvoiced[Visits_NoHrs] > 0)

if it doesn't there is something else in the data going on?  

eg.

this is the data i created

this works perfectly my measure here is

Rows > 0 = CALCULATE(COUNTROWS(Apartments),Apartments[Hours] > 0)

so i am not understanding why your data is not calculating properly.  You might need to dig a big deeper into the data.