Forum Discussion
Solved
DAX Question
I have a pivot table created through the data model. I am using measures on the pivot as columns. Actual and Active are measures. I want to calculate the total of Actuals where Active >0. What DAX...
Perhaps like this
where
# of Active := CALCULATE ( COUNT ( Data_AO[Student] ), Data_AO[Status] = "Active" ) //////////// # of Complete := CALCULATE ( COUNT ( Data_AO[Student] ), Data_AO[Status] = "Complete" ) /////////// # of Actual := [# of Active] + [# of Complete] ////////// # of Active Actual := IF ( HASONEVALUE ( Data_AO[Group No] ), [# of Actual], SUMX ( SUMMARIZE ( Data_AO, Data_AO[Group No], "actives", CALCULATE ( COUNTROWS ( Data_AO ), Data_AO[Status] = "Active" ), "completed", CALCULATE ( COUNTROWS ( Data_AO ), Data_AO[Status] = "Complete" ) ), IF ( [actives], [actives] + [completed], 0 ) ) )
Actual is also a measure ,
Actual = Measure "Active" + Measure "Complete".
Business scenario here is , this data set includes prehistorical data. Not always a group has active students in it. My aim is to aggregate the Actuals when the Active column / measure has a value.
Actual = Measure "Active" + Measure "Complete".
Business scenario here is , this data set includes prehistorical data. Not always a group has active students in it. My aim is to aggregate the Actuals when the Active column / measure has a value.
Perhaps like this
where
# of Active :=
CALCULATE (
COUNT ( Data_AO[Student] ),
Data_AO[Status] = "Active"
)
////////////
# of Complete :=
CALCULATE (
COUNT ( Data_AO[Student] ),
Data_AO[Status] = "Complete"
)
///////////
# of Actual :=
[# of Active] + [# of Complete]
//////////
# of Active Actual :=
IF (
HASONEVALUE ( Data_AO[Group No] ),
[# of Actual],
SUMX (
SUMMARIZE (
Data_AO,
Data_AO[Group No],
"actives", CALCULATE ( COUNTROWS ( Data_AO ), Data_AO[Status] = "Active" ),
"completed", CALCULATE ( COUNTROWS ( Data_AO ), Data_AO[Status] = "Complete" )
),
IF ( [actives], [actives] + [completed], 0 )
)
)
- Perfect, it worked although I might need to sit and understand the formulas.
Imi_Jay , glad to help. You may play with DAX Studio for better understanding of how formula works, e.g. evaluate SUMMARIZE only, etc.
- Thanks will do