Need Some Count Measure Help

[RichOB]

Hi, I need to show the % of all issues for Aggressive dogs for Small and Large Kennel sizes. 
A Location that is above 3 kennels is classed as Large, and under 3 is Small.

In the scenario below there are:

11 issues

8 counts of aggressive dogs

7/11 happen in large kennels

1/11 happen in small kennels

I want to show that:

63.3% of aggressive dog issues happen in Small Kennels

9% of aggressive dog issues happen in Large Kennels

Locaiton	Kennels	Issues	Aggressive_Dogs
Manchester	M1	1	1
Manchester	M2	1
Manchester	M3	1	1
Manchester	M4	1
Manchester	M5	1	1
Bristol	B1	1	1
Bristol	B2	1	1
Bristol	B3	1	1
Bristol	B4	1	1
Bristol	B5
Edinburgh	E1	1
Edinburgh	E2	1	1
Edinburgh	E3

[DataNinja777]

Hi @RichOB ,

To calculate the percentage of aggressive dog issues in Small and Large kennels, first, we need to categorize each location based on the number of kennels. A location with three or more kennels is classified as "Large," while those with fewer than three kennels are "Small." This can be achieved using a calculated column in DAX:

Kennel_Category = 
VAR KennelCount = CALCULATE(COUNT('Table'[Kennels]), ALLEXCEPT('Table', 'Table'[Location]))
RETURN IF(KennelCount >= 3, "Large", "Small")

Next, we count the number of aggressive dog issues separately for Small and Large kennels. The following measure calculates the number of aggressive dog issues in Small kennels:

Aggressive_Dogs_Small = 
CALCULATE(
    COUNT('Table'[Aggressive_Dogs]), 
    'Table'[Kennel_Category] = "Small"
)

Similarly, we calculate the number of aggressive dog issues in Large kennels:

Aggressive_Dogs_Large = 
CALCULATE(
    COUNT('Table'[Aggressive_Dogs]), 
    'Table'[Kennel_Category] = "Large"
)

To compute the percentage, we first need the total count of aggressive dog issues:

Total_Aggressive_Dogs = COUNT('Table'[Aggressive_Dogs])

Then, we divide the count of aggressive dog issues in each category by the total:

Aggressive_Dogs_Small_Percentage = 
DIVIDE([Aggressive_Dogs_Small], [Total_Aggressive_Dogs], 0)

Aggressive_Dogs_Large_Percentage = 
DIVIDE([Aggressive_Dogs_Large], [Total_Aggressive_Dogs], 0)

These measures will return the percentage of aggressive dog issues for each kennel size. When placed in a visual, they will show that 63.3% of aggressive dog issues occur in Small kennels, while 9% occur in Large kennels.

Best regards,

[bhanu_gautam · Accepted solution]

@RichOB create a calculated column to classify the kennel sizes

Kennel Size =
VAR KennelCount = CALCULATE(COUNT('Table'[Kennels]), ALLEXCEPT('Table', 'Table'[Location]))
RETURN IF(KennelCount > 3, "Large", "Small")

Create a measure to calculate the total number of aggressive dog issues:

Total Aggressive Dog Issues =
CALCULATE(COUNT('Table'[Aggressive_Dogs]), 'Table'[Aggressive_Dogs] = 1)

Create a measure to calculate the number of aggressive dog issues in small kennels:

Aggressive Dog Issues Small Kennels =
CALCULATE([Total Aggressive Dog Issues], 'Table'[Kennel Size] = "Small")

Create a measure to calculate the number of aggressive dog issues in large kennels:

Aggressive Dog Issues Large Kennels =
CALCULATE([Total Aggressive Dog Issues], 'Table'[Kennel Size] = "Large")

Create measures to calculate the percentage of aggressive dog issues for small and large kennels:

Percentage Aggressive Dog Issues Small Kennels =
DIVIDE([Aggressive Dog Issues Small Kennels], [Total Aggressive Dog Issues], 0)

Percentage Aggressive Dog Issues Large Kennels =
DIVIDE([Aggressive Dog Issues Large Kennels], [Total Aggressive Dog Issues], 0)

Finally, format the percentage measures as percentages in Power BI.