Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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.
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.
@Anonymous , Please find the attached solution after signature.
Check for unpivot in Data Tranformation
@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.
@Anonymous , you can take distinctcount. As Long it blanks it will not be counted.
https://www.dropbox.com/s/e5mqtn3tm3eow7n/WeekwiseData.pbix?dl=0
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |