Help calculating Percentage from in Matrix Visual

[MichaelRensing]

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

 

[MichaelRensing]

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.

[Mikelytics]

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

[MichaelRensing]

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%. 

[Mikelytics · Accepted solution]

Hi @MichaelRensing 

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.

[MichaelRensing]

@Mikelytics Thanks for this.  This is pointing me in the right direction.  Something still isn't quite right, but I will try to figure it out.  The 1st palce has a count of 15, and a percentage of 8.47, while 2nd has a count of 12 but a percentage of 10.58. The overall percentage should be 28, but is showing 3.5. If I can't I will @ you again.  Thanks again.

[MichaelRensing]

@Mikelytics 

The DIVIDE function was where the problem was. It should be the below.  Thank you for your assistance. I know feel more comfortable in working with Power BI.

RETURN
    DIVIDE (
        var_EntriesWithGrade,
        var_EntriesTotalAllPlace
    ) * 100

[Mikelytics]

Hi @MichaelRensing ,

ah! you right 😄 Thank you for the feedback and great that you found the error!

One small hint. Instead of putting "*100" into the formula you can also format it in percentage. When you click on a measure on the right side than on the top there shows up formatting options for your measure:

Best regards

Michael

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.

[Mikelytics]

Hi @MichaelRensing 

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.