Forum Discussion
COUNT and PERCENT over specific ROW
tviz21 - Perhaps a Measure such as:
Measure = CALCULATE(VAR Denominator = COUNTX(ALL(types),[Type]) VAR Numerator = COUNT([Type]) RETURN Numerator/Denominator)
Make sure to format as %.
tviz21 - For clarity, my table is called "types" and I have a single column in that table called "Type". I put the following data in it:
Type
1
2
3
2
3
My matrix shows:
Type Measure
1 20%
2 40%
3 40%
After playing around with this for a while, I ended up with -
Percent = CALCULATE(VAR Denominator = COUNTROWS(ALL('type'[Types])) VAR Numerator = COUNTA('type'[Types]) RETURN Numerator/Denominator
)
This gave me an overall percentage which I can filter for the different dimensions I have on my dashboard.
Thanks to both of you for your help! I'll definitely spend more time with both solutions to understand them better as I'm sure I'll need them later.
Toni
No need for CALCULATE() or variables in that measure. It should be identical to the much cleaner:
Percent = COUNTA( 'type'[Types] ) / COUNTROWS( ALL( 'type'[Types] ) )
CALCULATE() is only used to modify filter context or transform row context to filter context. Since this is a measure, there is already filter context coming in from the report and the visual's labels, and there is no row context in a measure, except within an iterator function.
The variables are likewise superfluous, because there is no alternate context in which you reference them. If a variable is defined in the same context as it is referenced in, then it's exactly the same as just using the variable definition in-line.
