Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Formula and Measure Question

Hello everyone! My data is depicted below (1st picture). It is pretty simple, I created the Total Subtasks Completed and the % of PP Progress columns. The total subtasks completed is the sum of the all the different subtask result columns, and the % of PP progress uses the values in the Total Subtasks completed columns divided by 17 because thats how many different subtasks there are. On the second picture I attached, I made a quick table visual which shows how many subtasks and their % of PP progress a person has. I was looking to create a measure or column which can bucket the % of PP Progress into a 25% bucket. Such as those between 0 and 25% are in 1, 26-50% in another, 51-75% in a third, and lastly 76-100%. Is there a DAX Calculation I can use to make this successful?

 

Need Help
question
Show & Tell
  • FreemanZ's avatar
    FreemanZ
    3 years ago
    Supposing your table named Data, try to add a column with the code below:
    PctRange =
    SWITCH(
        TRUE(),
        Data[% of PP Progress] <=0.25, "0-25%",
        Data[% of PP Progress] <=0.5, "26%-50%",
        Data[% of PP Progress] <=0.75, "51%-75%",
        "76%-100%"
    )

6 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    3 years ago

    You can group your data without DAX! Click the column you're wanting to group and under column tools > data groups > New data groups


    Then you can toss those %s in buckets

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I never knew this! When I tried to do it, I got this popup which I've never seen before. 

       

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago
    Supposing your table named Data, try to add a column with the code below:
    PctRange =
    SWITCH(
        TRUE(),
        Data[% of PP Progress] <=0.25, "0-25%",
        Data[% of PP Progress] <=0.5, "26%-50%",
        Data[% of PP Progress] <=0.75, "51%-75%",
        "76%-100%"
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I got this error when I tried it <ccon>A circular dependency was detected: PP-Task Completion[% of PP Progress], PP-Task Completion[PctRange], PP-Task Completion[% of PP Progress].</ccon>

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago
        Rewrite your column as below:
        % of PP Progress =
        CALCULATE(
            DIVIDE(Data[Total Subtasks completed],17),
            ALLEXCEPT (Data[Total Subtasks completed])
        )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    Try a measure like below:

    Measure =
var %Progress = sum([% of PP Progress])
Return
SWITCH(
    TRUE(),
    %Progress <= 0.25, 1,
    %Progress > 0.25 && %Progress < = 0.5, 2,
    %Progress > 0.5 && %Progress <=0.75, 3,
    4
)