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.
I am having a hard time figuring out how to do a simple % over a specific row in a Matrix - thought it would just be a simple calculation but apparently it's not that easy?
I have a matrix with 3 rows -
Category
Comment1
Comment2
Value is
COUNT of 'Type'
I just want to show COUNT of 'Type' as a % of only the 'Comment1' Row total - how can I do that one? I could not find any examples of this unless I'm searching incorrectly.
Thanks in advance for any help with this, new to Power BI
Toni
The Matrix is just the visualisation. The DAX formula will depend on the table structure in the data model.
So, assuming you have a table called 'Data' with a column called [Type], you could write these 2 DAX Measures
Line Count = countrows(Data[Type]) % of Comment 1 = divide([Line Count],calculate([line Count],data[type]="Comment 1"))
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
100 | |
83 | |
76 | |
65 |
User | Count |
---|---|
120 | |
111 | |
94 | |
83 | |
77 |