DAX equivalent of pivot table calculated field that divides 1 column by another

[kcmuppet]

Hi,

It seems calculated fields are greyed out if your data source for a pivot table is a power query that has was created with the option 'Add to Data Model' selected.

In that case how do I achieve (in DAX?) the equivalent of adding a calculated field that simply divides one column by another to effectively show a percentage.

For example, for a pivot table that has Department names as rows, a field counting staff, I'd like to add a calculated field that is a count of the staff who have completed training and show it as a % of the count of the staff in that department - i.e.

Department​	Staff (count by department)​	Sum if Date Completed Training is not blank​	Completed Training​
Department 1​	33​	17​	52%​
Department 2​	23​	21​	91%​
Department 3​	67​	15​	22%​

Could somebody point me in the right direction (I'm completely new to DAX)

Thanks

 

[RoryA]

What does the source data look like? Your second data column appears to be a sum rather than a count, judging by its title.

Generally you would create an explicit measure for each of the two fields, then create a third measure that simply divides one measure by the other.

 

[kcmuppet]

Hi, thanks for your reply.

The source data would be like this (M example below):

User ID	Department	Date training completed
User ID 1	Department 1	10-Mar​
User ID 2	Department 2	22-Jan​
User ID 3	Department 3
User ID 4	Department 2
User ID 5	Department 3	11-Feb​
User ID 6	Department 1
User ID 7	Department 2	10-Mar​
User ID 8	Department 3
User ID 9	Department 3

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVLwdFEwVNJRckktSCwqyU3NKwFzDQ10fROLlGJ1EKqMUFWBuEZGul6JeSiqjFFVgbgo8iaYpqDIm2LqNzTUdUtNQlFlhuliFHlzTFuw+MiCgFstscnHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Department = _t, #"Date training completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", type text}, {"Department", type text}, {"Date training completed", type date}})
in
    #"Changed Type"

Yes in my first post I showed a sum (which should probably be a count) as an interim step, but I don't need to display it. I just need a (pivot) table with the 3 columns: Department, total staff members, % that have completed training (countif?) the 'Date training completed' in the source Table is a date/not null for each department - like column the manually created column D

I'm not sure what is meant by explicit measures, or where to define them. I assume I create pivot table as normal first then on the Ribbon, Power Pivot > Measures > New Measure.

What I'm confused about is that having done that, I'm presented with an option to create a measure which seems to be only applicable to the source data table, not the pivot table (and I'm not really sure how to start defining the measure/s to create the pivot table column):

 

[RoryA]

I would create three measures:
1. UserCount with formula: =COUNTA(Table1[User ID])
2. TrainingCount with formula: =COUNT(Table1[Date training completed])
3. TrainingPct with formula: =[TrainingCount]/[UserCount] (you can format as number - percentage in the measure dialog)

 

[kcmuppet]

Thank you, that's a great help.
Am I right in assuming your rationale for creating 3 measures instead of just one =[Count of Date training completed]/[Count of User ID]
is so that the other columns don't need to be displayed in the pivot table?

 

[RoryA]

The others are implicit measures anyway, but I like to create explicit measures for each step of a calculation so that if any of them need changing later, I only have to do it in one place - everything else that uses the same calculation refers to the same measure.

 

[kcmuppet]

I see now - that makes sense, thanks for the explanation