cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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.

1 ACCEPTED SOLUTION
Super User

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors