cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Add Row Percentage

I need to add a colomn  Matrix that calculate the percentage (%) of UNITs "ON MOVE":

 Matrix.png

 

I almost got it, the problem is that it calculate it for all the columns. 

 

How Can I Calculate the % of STATUS = ON MOVE by UNIT

 

I asume that it can be done using a measure, may be something like this: %OnMove= SUM(Inventory[STATUS1] = 'ON MOVE'/TOTAL by UNIT

 

I've been using PowerBI for less that 2 weeks, so please consider this in your response (asume I know nothing)

 

6 REPLIES 6
Highlighted
Frequent Visitor

Re: Add Row Percentage

@ewill_bdg You can create a measure, such as Measure = DIVIDE([ON MOVE],[Total Sum of Available]) then convert it to a % format by clicking the measure in the Fields list then going to the Modeling tab in the top left.

Highlighted
Regular Visitor

Re: Add Row Percentage

ERROR:

The value for 'ON MOVE' cannot be determined. Either 'ON MOVE' doesn't exist, or there is no current row for a column named 'ON MOVE'

 

Same for: Total Sum of Available

Highlighted
Regular Visitor

Re: Add Row Percentage

I've found this: http://community.powerbi.com/t5/Desktop/percentage-calculator-on-Row-wise/m-p/86473#M36283

 

I removed all columns from VALUES and left only STATUS (ON MOVE, AVAILABLE, DAMAGE, etc.),

and tried: %Avail = [ON MOVE]/[TOTAL]

 

SLA Not Met % = [SLA Not Met] / [Total]

but for some reason, I receive an ERROR, "ON MOVE" is not a column.

Highlighted
Frequent Visitor

Re: Add Row Percentage

@ewill_bdgCreate a measure of the On Move Sum of Available. 

Sum of On Move Available = SUMX(On Move, Sum of Available)

 

I can't tell what Table the Total Sum Available is in or if that is a table itself. You may need to provide more code there, but I think you will then need to do

 

Divide([Sum of On Move Available],[Total Sum of Available])

 

You can then filter it by unit within your report

Regular Visitor

Re: Add Row Percentage

This is how i have configured the Matrix:

 

Row: UNITS

 

Column: STATUS1

 

Values : SUM[STATUS1]

            COUNT[STATUS1]   show value as percentage row of total

 

I hope this helps.

Highlighted
Frequent Visitor

Re: Add Row Percentage

@ewill_bdg

 

Yes that helps me understand a little more. 

 

You will do this Measure first. This should get the Sum of a Status. I am still a little confused on your fields so I just did a basic template. The table should be the table your status is in and column should be the status. The Table is what you called your data when you orignally loaded it into Power Bi. For example, the table is Sheet 1. The columns I have are Alphabet and Number. And the values are in Alphabet and Number. So if I wanted the Sum of letter a I would do 

Sum of Letter = CALCULATE (
COUNT (Sheet1[Alphabet]),Sheet1[Alphabet]="a")

.

capture5.PNGcapture6.PNG

 

Sum of On Move Available  = CALCULATE (
COUNT (Table[Coulmn]),Table[Coulmn]="value")

 

I think what you need to do next is create a second measure that finds the SUM of all avilable between all the different statuses. 

This would be 

CountValues = CALCULATE ( COUNT ( TableName ), TableName[ColumnName])

 

then you would do the divide.

 

Sum of % = DIVIDE([Sum of On Move Available],[Sum of All Available])

 

This would give you the % of only On Move. You could change up the first measure created per value.

 

I hope this helps!

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors