Forum Discussion
Help calculating Percentage from in Matrix Visual
I am looking to calculate percentages in a Matrix visual. I have rows of entries, with a count of places, and a total count. I am looking to calculate what perntage of entries recieved an award.
Snip of the Matrix
Snip of the Visual and Table the data is coming from
ok,
so instead of draging the columns into the value field of the matrix please create the following measure:
Percentage of entries with grade = VAR var_EntriesWithGrade = CALCULATE ( COUNTROWS ( TableName ), FILTER ( TableName, TableName[Place] <> "" && NOT ISBLANK ( TableName[Place] ) ) ) VAR var_EntriesTotalAllPlace = CALCULATE ( COUNTROWS ( TableName ), ALL ( TableName[Place] ) ) RETURN DIVIDE ( var_EntriesTotalAllPlace, var_EntriesWithGrade )After creating the measure you should find it somewhere on the right side betweenor in the tables.
Now please create a matrix visual, put the style into the rows and the measure into the values.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
8 Replies
The very easy solution COULD be to do the folloing setting in your value field:
If this does not work or does not bring the intended result then you might need to go the standard way to build measures.
Please create two measures a measure
1) Count of places
Count of Places = COUNTROWS(AllYears)2) precentage of places
Percentag of places = var var_CountPlaces = [Count of Places] var var_AllPlaces = CALCULATE( [Count of Places], ALLSELECTED(AllYears[Category]), ALLSELECTED(AllYears[Style]) ) RETURN DIVIDE(var_CountPlaces,var_AllPlaces,BLANK())To better help you I would need to understand the whole business logic and exactly what you want to see in the table, in best case with an example of input, business logic and output.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Thanks for the quick Reply. Unfortunately neither worked. When Trying to show as a percentage, I am setting Count of Place to a Percentage of Row. It will displays as 100%, discarding all the blank values.
Fo example, the first non bold row. There are 91 elements that have Place blank. 15 with the value 1st in Place, 12 with the value 2nd in Place, and 9 with the value 3rd in Place. 91+15+12+9 = 127 which is the Count of Style Value. 15+12+9 = 36 which is the Total Count of Place Value.
I am looking to calculate the Percentage of entries that have a Place value that is not blank. This would be (Total Count of Place / Total Count of Style) * 100
When trying to add the measure, I recieved an error on the CALCULATE command, it didn't like [Count of Places] I will need to dig into that further.HIiu MichaelRensing
As you can see
Total Count of Place
and
Total Count of Style
have every time the same count. you can see that in the picture below
for each color style and place have the sam count.
This is because they are in the same table and there are attribute values and not of amounts. So the formula you describe would everytime lead to the result 1% because
To solve your problem we would neet to start from the beginning. Very simple. Please show a small sample of your source table (not the visual, more the raw data).
Then show an exemplary calculation and the expected result.
Best regards
Michael
Michael
Here is a example of the data. There is a Column for Competition, For Place (either blank, 1st, 2nd, or 3rd) Category, Style, Brewer, and Date.
The Place, Category, and Style columns are all set to Count for Summation.
I am looking to get a percentage of each style that has a value other than blank in the Place column. So Summation of all of a specific Style, and then Summation of not Blank. So from the previous screen shot, there are 91 entrie of Style 1A. American Light Lager that have a blank value for Place, 36 entries of Style 1A. American Light Lager that have a value of 1st, 2nd, or 3rd for place. There are a total of 127 (91+36) entries of Style 1A. American Light Lager. The calculation of percentage of entries of that style that have a value in Place would be (36/127)*100 = 28.35%.
