Forum Discussion

Imi_Jay's avatar
Imi_Jay
Brass Contributor
Jun 07, 2022
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...
BI & Data Analysis
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 17, 2022

    Imi_Jay 

    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 )
    )
)
Imi_Jay's avatar
Imi_Jay
Brass Contributor
Jun 15, 2022
My measure are different to what you have produced. Apologies I didn't explain the data set well. In the data set there is a column "Status" which has "active and completed as status.

Hence my measure are as follows,
Measure "Active" = CALCULATE(COUNT(Data_AO[Student]),Data_AO[Status]="Active")
Measure "Complete" = CALCULATE(COUNT(Data_AO[Student]),Data_AO[Status]="Complete")

There is no column as such "Active" or complete. In that case how can I write the formula. That's where I'm stuck. Thanks for the help!
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 15, 2022

Imi_Jay 

And what is Actual - is that column or measure and how do you calculate it?

  • Imi_Jay's avatar
    Imi_Jay
    Brass Contributor
    Jun 17, 2022
    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.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 17, 2022

      Imi_Jay 

      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 )
    )
)
      • Imi_Jay's avatar
        Imi_Jay
        Brass Contributor
        Jun 21, 2022
        Perfect, it worked although I might need to sit and understand the formulas.

Resources