Count total value in a column as 1.

[Anonymous]

I'm trying to figure out a way to just count a total value in a certain week as "1". To illustrate:

Person   Week 1    Week 2    Week 3    Week 4    Week 5     Count

A            6              8              9                              3               4

B            6              8              9              4              3               5

C            6                             9                                                2

Preferably I'd only have the count up in a measure, so that I can show a total next to the count of the weeks:

Person  Total  Weekcounter

A           26      4

B           31      5

C           15      2

How do I calculate such a thing?

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on you description, I created data to reproduce your scenario.

Table:

You may go to 'Query Editor', make weeks selected, click 'Transform' ribbon=>'Unpiot columns', then 'Close and Apply'.

Then you may create two measures as below.

Weekcounter = 
CALCULATE(
    DISTINCTCOUNT('Table'[Week]),
    FILTER(
        ALL('Table'),
        'Table'[Person] = SELECTEDVALUE('Table'[Person])
    )
)

Total = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Person] = SELECTEDVALUE('Table'[Person])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Just in case you want a Query editor solution, then this M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Week 1", Int64.Type}, {"Week 2", Int64.Type}, {"Week 3", Int64.Type}, {"Week 4", Int64.Type}, {"Week 5", Int64.Type}, {"Count", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person", "Count"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Person"}, {{"Total", each List.Sum([Value]), type number}, {"Count", each List.Min([Count]), type number}})
in
    #"Grouped Rows"

Hope this helps.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on you description, I created data to reproduce your scenario.

Table:

You may go to 'Query Editor', make weeks selected, click 'Transform' ribbon=>'Unpiot columns', then 'Close and Apply'.

Then you may create two measures as below.

Weekcounter = 
CALCULATE(
    DISTINCTCOUNT('Table'[Week]),
    FILTER(
        ALL('Table'),
        'Table'[Person] = SELECTEDVALUE('Table'[Person])
    )
)

Total = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Person] = SELECTEDVALUE('Table'[Person])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Please find the attached solution after signature.

Check for unpivot in Data Tranformation

[Anonymous]

@amitchandak Thank you for the attachment. But how did you create the count column? And does the way the count column is set up take into account that 1 week could have multiple rows? So for example:

Person A has clocked 1 hour on day 1 and 4 hours on day 5 for week 1. The total for week 1 is 5 hours, but the week count should be 1, not 2. 

So basically the formula/column has to check if the hour column >0 for a certain week and then count that week for that person as 1.  In Excel I can think of at least 5 different ways to do this (either by adding a column or just 1 bigger formula), but in PBI I'm drawing a blank.

EDIT: Cant I just do something along the line of Count = COUNTROWS(FILTER(DURATION COLUMN, DURATION COLUMN [HOURS] > 0) as @Greg_Deckler suggested? I mean the above formula doesnt work for me as it counts lines based on the days (even though the matrix is based on weeks), but I can imagine it should be close to it. 

[amitchandak]

@Anonymous , you can take distinctcount. As Long it blanks it will not be counted.

https://www.dropbox.com/s/e5mqtn3tm3eow7n/WeekwiseData.pbix?dl=0

[Greg_Deckler]

Well, normally you would probably unpivot those week columns and then it is just a matter of COUNTROWS but if for some reason you need the data in that format, it is a big ugly IF(NOT(ISBLANK([column])).... kind of thing. Ugly. I would vote for unpivot.

[Anonymous]

If I went for unpivoted week columns, how would I go about it? And how is COUNTROWS different from adding the value to the matrix and setting it to count?

My data is workhours per day, which I would want to "combine" to the week and then count it as 1. I basically just want to know how many weeks a certain person has clocked hours in, in a certain timeframe.