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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

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.