cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nsolanki Frequent Visitor
Frequent Visitor

Row to Column and Divide that column

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. image.pngSample Data

 

 

5 REPLIES 5
sturlaws New Contributor
New Contributor

Re: Row to Column and Divide that column

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 Frequent Visitor
Frequent Visitor

Re: Row to Column and Divide that column

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. 

 

image.png

sturlaws New Contributor
New Contributor

Re: Row to Column and Divide that column

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?

Highlighted
nsolanki Frequent Visitor
Frequent Visitor

Re: Row to Column and Divide that column

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

 

image.png

 

sturlaws New Contributor
New Contributor

Re: Row to Column and Divide that column

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

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,058)