Grouping

[JunedS]

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
1	100	05-02-2024	29-02-2024	Below Limit
1	100	01-03-2024	31-03-2024	Above Limit
1	100	04-02-2024	05-02-2024	Within Limit
1	100	05-02-2024	29-02-2024	Below Limit
2	100	01-03-2024	31-03-2024	Above Limit
2	100	04-02-2024	05-02-2024	Within Limit
1	100	01-03-2024	31-03-2024	Above Limit

The output that I am looking is like this, 

Base Code	Bucket Start Date	Bucket End Date	Limits
100	01-03-2024	31-03-2024	3(Above Limit)/3 (unique values) = 100%
100	05-02-2024	29-02-2024	2 (Below Limit)/3 = 66.67%
100	04-02-2024	05-02-2024	2 (Within Limit)/3 = 66.67%

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

[amitchandak · Accepted solution]

@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()
)