Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JunedS
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 
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. 

1 ACCEPTED SOLUTION
amitchandak
Super User
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()
)

View solution in original post

1 REPLY 1
amitchandak
Super User
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()
)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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