Row to Column and Divide that column

[nsolanki]

Hello,

I have created matrix table that looks like pivot table (Row and column). Since column are created from rows i cannot divide this newly created cloumn. I wanted to divide 'WS" with "Total" and similarly "Other Billable" with "Total". 

Please help me out with this. I am new to Power BI. 

Thanks. Sample Data

[sturlaws]

Hi,

since you have not included any information about table names and fields, a solution will have to be somewhat generic. 

Try something like this:

[ws by total] =
DIVIDE (
    CALCULATE ( SUM ( table[value] ); FILTER ( table; type = "WS" ) );
    CALCULATE ( SUM ( table[value] ); ALL ( table[type] ) ))

How to go from here will depend on what you want the outcome to look like. Should WS be replaces with WS by Total, or do you want to show both?

[nsolanki]

Hello,

My bad that i haven't provided all the information. The new column (WS/Total) will not replace but as addition of new column to my matrix table. 

see below are feilds of my table.  

my intent is to create new caculated column showing utilization (WS/Total) similarly Other Billable/Total etc. but as you can see from my table there is no direct fileds (called as WS or other billable) that i can divide stright a way. 

much appreciated your help in this regards. 

[sturlaws]

In the screenshot in you first post, you have [GA to US], [Idle time], [Non Billable], [Other Billable] and [WS]. What are those? Fields/columns in another table? Measures?

[nsolanki]

[GA to US], [Idle time], [Non Billable], [Other Billable] and [WS] are the rows underneeth filed [RM]. What i have done is the created

matrix by putting [RM] under column as highlighted in below snap shot. So i had different number of hours that have been charged on different rows which i provided remarks (GA to US, Other BIllable, WS and so on..) and based on that it comes on columner form. 

[sturlaws]

Ah, yes, off course, my bad, I should have figured out that myself.

What I would suggest for you to do, is to create a new table, called e.g. dimRM. dimRM should contain all the values og 'Sheet1'[RM] as well as [WS by Total] and other calculations you want to see. This table you can create by using the New Table-function in the Modelling tab of PBI Desktop:

dimRM =
UNION ( DISTINCT ( 'Table'[RM] ); { "WS by Total"; "Other" } )

Create a relationship between this table and 'Sheet1' on RM.

Now create a measure that would look like this

measureHours =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( 'dimRM'[RM] ) = "WS by Total"; [WS by Total];
    SELECTEDVALUE ( 'dimRM'[RM] ) = "Other"; [Other];
    SUM ( 'Sheet1'[Hours] )
)

where [WS by total] and [Other] are measures. Now replace 'Sheet1'[RM] with 'dimRM'[RM], and use measureHours as measure in you table visual