Pivot table and then divide columns

[Anonymous]

I have a pivot table that looks like this:

I am trying the replace column 'WK 1' with 'WK 1' / 'Num of Active Users' * 100 (expressed as %). Additonally, I am trying to do a conditional BG format only on rows WK 1 to WK 5. How can I do so?

Utilmately, I want my end product to look like this.

I did this by instead of forming the pivot table as a matrix, I did the pivot in the edit query and was able to do calculations on each individual column. The data that I am pivotting looks like this:

My pivot operation looks like this:

[AnthonyTilley]

Can you provide some sample data 

Or better yet a sample PBIX file 

[AnthonyTilley]

sorry not sure if im missing something here but the sample data you provided does not ahve anything realting to number of active users 

[AnthonyTilley]

okay inorder to get your values into the the table use the follwoing measure 

Measure = 
--get number of active users
var activeusers = CALCULATE(sum('Copy of temp (1)'[cnt_pax]),'Copy of temp (1)'[week_diff] = "NUM OF ACTIVE USERS")
--get value to divide by
var cnt_p = sum('Copy of temp (1)'[cnt_pax])
--find the colunm header value
var sv = SELECTEDVALUE('Copy of temp (1)'[week_diff])
-- calculate teh value divided by the active users * 100
var diff = DIVIDE(cnt_p,activeusers)*100
-- switch the value based on the header colunm, for active user header use active user value for all others user DIFF
var ret = SWITCH(sv,"NUM OF ACTIVE USERS",activeusers,diff)
-- return the values
return ret

just working on how to conditonally format without including the first colunm

[AnthonyTilley · Accepted solution]

in order to apply the formatting you where after.

Create a seconf measure called formating as below 

it is nearly identical to my first measrue but in this version in the switch statment the active users colunm is replaced with blank 

Formatting = 
--get number of active users
var activeusers = CALCULATE(sum('Copy of temp (1)'[cnt_pax]),'Copy of temp (1)'[week_diff] = "NUM OF ACTIVE USERS")
--get value to divide by
var cnt_p = sum('Copy of temp (1)'[cnt_pax])
--find the colunm header value
var sv = SELECTEDVALUE('Copy of temp (1)'[week_diff])
-- calculate teh value divided by the active users * 100
var diff = DIVIDE(cnt_p,activeusers)*100
-- switch the value based on the header colunm, for active user header use active user value for all others user DIFF
var ret = SWITCH(sv,"NUM OF ACTIVE USERS",blank(),diff)
-- return the values
return ret

then in the conditional formating section select background colour on, and in the advanced options use the follwoing settings 

the Based on filed use the new formating measure, and make sure that the default formatting is set to dont fomrat 

then ensure that diverging is ticked and select your three colurs 

[Anonymous]

@AnthonyTilley Could you please tell me a little more about the switch statement as I would like to understand your code in order to replicate it in future. Thanks!

[AnthonyTilley]

So the Switch Statement allows you to change values based on an expression

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])  

so for example a simple switch statement would be as follows

lets say we had a measure for sum(orders value)

we could create a switch statement that says 
--this first bit says that we will switch based on the value of the sum

Switch(SUM(ORDERS VALUE),
-- we then add any number of conditions and the value that they will become
10,"small",
20,"mid"

30, High
--the final value is the default value that is used for anything that is not in the conditions above

,"OTHER")

so in the example above i have said switch based on the sum of my orders value 

if the some = 10 then replace with the word "Small"

if it equals 20 then mid and 30 then high anything else say other 

if i then use this measure in a piviot it will give eaither small, mid, high or other based on the value 

so if the total order value was 21 it would say other 

you can use less than or greater than or any other operation to check against

in your example 

var ret = SWITCH(sv,"NUM OF ACTIVE USERS",activeusers,diff)

--we are saying to switch based on the value of SV (sv is a veriable where i am holding the value in the header the selected value

Switch(sv,
-- im then saying if the value in sv  = "NUM OF ACTIVE USERS" then use the value stored in the acctive users veriable
"NUM OF ACTIVE USERS",activeusers,

-- i then finish off the function by saying if it is anything else (in this case WK1, WK2,Wk3,Wk4,WK5) then use the value stored in the diff veriable

,diff)

you can use the switch function for many things for example to replace a month value with a word

=SWITCH([Month], 
1, "January", 
2, "February", 
3, "March", 
4, "April" ,
5, "May",
6, "June", 
7, "July", 
8, "August", 
9, "September", 
10, "October", 
11, "November", 
12, "December"  
               , "Unknown month number" )  

if you want to use operations such as greater than or less than then you can use a trick using the True() method

in this example we replace the expresion with the true function TRUE(), now instead of having a fixed value in the conditions such as 10,20,30 we can replace this with any expression 

 = SWITCH(
          TRUE(),
          [MyMeasure]<1,expr1,
          [MyMeasure]<2,expr2,
          [MyMeasure]<3,expr3,
         …)

for more info on the true function check out this article

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

for more info on the switch function check out the link below

https://docs.microsoft.com/en-us/dax/switch-function-dax

[AnthonyTilley]

Here is a sample file 

sample file