calculate a new column using two related columns and the values of the 3rd column

[Anonymous]

Hello Team, 

Im trying to calculate a new column using two related columns and the values of the 3rd column. The problem is that I have first column (Alias) and it has redundant data and the second (Time Type) has related data to the cells of that of the first column (Alias). Both the first and the second are text values.  The third however (Measure x) has number values. how can I make the calculation for such a table ? I need to sum all the specific Alias activity for example AIBRAHIM69 I need to sum the following (Day of Rest, Job, and On) then I need to the Job value for  AIBRAHIM69 to be devided by the sum of the time type of the  AIBRAHIM69 (Day of Rest, Job, and On). Basically for AIBRAHIM69 it will be (7/19+7+5). so how can I calculate that ?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If the [Meaure x] is a column,

for example, in my test, [value] represents the [Meaure x], 

to get results, create a measure

job/sum =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[alias] ), 'Table'[timetype] = "job" )
    ),
    CALCULATE ( SUM ( 'Table'[value] ), ALLEXCEPT ( 'Table', 'Table'[alias] ) )
)

If [Measure x] is a measure, create a measure

job/sum 2 =
DIVIDE (
    SUMX (
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[alias] ), 'Table'[timetype] = "job" ),
        [Measure]
    ),
    SUMX ( ALLEXCEPT ( 'Table', 'Table'[alias] ), [Measure] )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kentyler]

i made a simplified sample table and a measure to do the calculation

the report looks like this

and the measure like this

TimeTypePercent =
var alias = max(dayofrest[Alias])

var hoursfortype = max(dayofrest[x])

var total_hours = calculate(sumx(dayofrest,dayofrest[x]),All(dayofrest),dayofrest[Alias] = alias)

var percentbytype = format(divide( hoursfortype,total_hours),"percent")

return percentbytype
OR

TimeTypePercent =
var alias = selectedvalue(dayofrest[Alias])

var hoursfortype = selectedvalue(dayofrest[x])

var total_hours = calculate(sumx(dayofrest,dayofrest[x]),All(dayofrest),dayofrest[Alias] = alias)

var percentbytype = format(divide( hoursfortype,total_hours),"percent")

return percentbytype
both seem to return the same result
first i save 2 variable that have the alias and the hours in the current row
then i sum up all the hours for the alias
then i divide the hoursfortype by the total_hours

the first dax problem is how to refer to the values of the "current" row before you start looping over the table... since the table visual means that only one row is in the row context, MAX operates on just that row and gets the value for you

then you call calculate and inside of calculate you use an interator, sumx, to loop over the table and add up the "x" values, but only for the rows where the alias matches the value you have in your variable

you call formate when you do the division to convert the answer into a percentage

hope that helped

Ken Tyler 7 slogans to help your power bi practice 

[Anonymous]

Hello Kentyler,

the solution you sent is for a hard coded table. The data that I'm dealing with is from a sharepoint list. how can I apply this on that ?