Count values depending on a condition

[jorismo]

Hi all,

my next question 🙂

I've got a table with dates and available status (1 and 0.5).

What I want to achieve is when I plot the values in an area chart I see that on the 4the of january, 4.5 persons were available. On the 5th 8 persons were available.

First Login                        Total Time             Available Day

In first case I was thinking to use the countRow function but that would be a problem because the 0.5 (half day) wouldn't be counted correctly (would be counted as 1)

Thanks again

[v-shex-msft]

HI @jorismo, 

If you use numeric value to record available days, I think you can use sum function to get the total available days. 

Sample:

Total available days =
CALCULATE (
    SUM ( table[avaliable days] ),
    FILTER ( ALL ( table ), table[First login] = MAX ( table[First login] ) )
)

In addition, you can also add filters to calculate with available day as the parameter of calculation. ( countrows *1 + countrows * 0.5)

Regards,

Xiaoxin Sheng

[jorismo]

Hi I can't get it working.

The result that i want is that I can plot this to an area chart where also some kpi's are shown.

Result:

First login                 Available

4/01/2017                  4,5

5/01/2017                  8

Hope that you can give me some more advice thanks!

[v-shex-msft · Accepted solution]

Hi @MFelix,

Maybe you can try to use summarize function to create a summary table:

Table = SUMMARIZE(Table1,[Date],"Total Day",SUM(Table1[Avaliable Days]))

Regards,

Xiaoxin Sheng

[MFelix]

Hi @jorismo,

Just do the sum on available day, should give you what you need.

Regards,

MFelix