Forum Discussion

JunedS's avatar
JunedS
Frequent Visitor
2 years ago
Solved

Grouping

This is the sample data that I am having, now I want to group the activities based on their 'Basecode', 'Bucket Start Date', and 'Bucket End Date', and then calculate the average count of limits within each group. I want to represent this information in a stacked chart, where the x-axis shows the time intervals (bucket weeks) and the y-axis displays the average count of limits. I cannot use the Group by function and removing duplicates as it will result in loss of other data at granular level. I want to create a measure which do the calculation for me. 

  

 Base Code Bucket Start Date Bucket End Date Limits 
110005-02-202429-02-2024Below Limit
110001-03-202431-03-2024Above Limit
110004-02-202405-02-2024Within Limit
110005-02-202429-02-2024Below Limit
210001-03-202431-03-2024Above Limit
210004-02-202405-02-2024Within Limit
110001-03-202431-03-2024Above Limit 

 

The output that I am looking is like this, 

 

 Base Code Bucket Start Date Bucket End Date Limits
10001-03-202431-03-20243(Above Limit)/3 (unique values) = 100%
10005-02-202429-02-20242 (Below Limit)/3 = 66.67%
10004-02-202405-02-20242 (Within Limit)/3 = 66.67%


I saw we can do this using Summarize and AverageX function. Any help would be greatly appreciated. 

Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    2 years ago

    JunedS , Based on what I got, You need a measures like

     

    Total Unique Limits =
    DISTINCTCOUNT('Table'[Limits])


    Count Above Limit =
    CALCULATE(
    COUNTROWS('Table'),
    'Table'[Limits] = "Above Limit"
    )

    Count Below Limit =
    CALCULATE(
    COUNTROWS('Table'),
    'Table'[Limits] = "Below Limit"
    )

    Count Within Limit =
    CALCULATE(
    COUNTROWS('Table'),
    'Table'[Limits] = "Within Limit"
    )


    Percentage Above Limit =
    DIVIDE(
    [Count Above Limit],
    [Total Unique Limits],
    BLANK()
    )

    Percentage Below Limit =
    DIVIDE(
    [Count Below Limit],
    [Total Unique Limits],
    BLANK()
    )

    Percentage Within Limit =
    DIVIDE(
    [Count Within Limit],
    [Total Unique Limits],
    BLANK()
    )

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    JunedS , Based on what I got, You need a measures like

     

    Total Unique Limits =
    DISTINCTCOUNT('Table'[Limits])


    Count Above Limit =
    CALCULATE(
    COUNTROWS('Table'),
    'Table'[Limits] = "Above Limit"
    )

    Count Below Limit =
    CALCULATE(
    COUNTROWS('Table'),
    'Table'[Limits] = "Below Limit"
    )

    Count Within Limit =
    CALCULATE(
    COUNTROWS('Table'),
    'Table'[Limits] = "Within Limit"
    )


    Percentage Above Limit =
    DIVIDE(
    [Count Above Limit],
    [Total Unique Limits],
    BLANK()
    )

    Percentage Below Limit =
    DIVIDE(
    [Count Below Limit],
    [Total Unique Limits],
    BLANK()
    )

    Percentage Within Limit =
    DIVIDE(
    [Count Within Limit],
    [Total Unique Limits],
    BLANK()
    )