Problem creating a calcualted column from diferent columns, Help needed

[Borjaveigasvh]

Good Mornig all,

I have a doubt, Iknow I can either unpivot or create a column in M, but my problem is, that I need to have the data pivoted and some of the collumns come from a calculated column not from de dataset.

I have a N assets in the datast. sevreal columns give us diferent valuations for that asset (appraisal, appraisal2,  appraisal3, appraisal4 market value, etc) 

Therefore to get a variation of coeficient, I need to make a calculation that gets all the columns standard deviation and divide it by the average of their value.

Does enyone have an idea for this matter?

Many thanks 

Borja Veiga 

[Anonymous · Accepted solution]

Hi @Borjaveigasvh 

You can "unpivot" with dax as long as you don't expect dynamic creation of fields. Something like this will create a calcuated (unpivotted) table and from there on you can use the normal static functions.

UnPivotted = 
GENERATE(
    CROSSJOIN(
 GENERATESERIES(1,5,1), // 5 the number appraisal fields you have
 ALL('Table'[Asset]) 
 ),
    var _Asset = 'Table'[Asset]
    var _Field = 
    switch(
        TRUE(),
        [VALUE]=1,LOOKUPVALUE('Table'[f1],'Table'[Asset],_Asset), // your appraisal fields
        [VALUE]=2,LOOKUPVALUE('Table'[f2],'Table'[Asset],_Asset),
        [VALUE]=3,LOOKUPVALUE('Table'[f3],'Table'[Asset],_Asset),
        [VALUE]=4,LOOKUPVALUE('Table'[f4],'Table'[Asset],_Asset),
        [VALUE]=5,LOOKUPVALUE('Table'[f5],'Table'[Asset],_Asset)
        )

 return
     ROW(
     "FieldValue",_Field
     )
    )

You need to tune the tabel defintion a bit, standard my FieldNumber column is called [Value].

Hope this helps,

Jan

if this is a solution for you, don't forget to mark it as such. thanks

[Borjaveigasvh]

Hello JustJan I have to thank you, after implementing your solution, it does work!

I have some issues, because since the appraisals and values come from different tables,when In implement this formula :

Cx desviación = DIVIDE( CALCULATE(STDEV.P(UnPivotted[FieldValue]),filter(UnPivotted,FILTERS(MAESTRO[SOCIEDAD_UR])=UnPivotted[SOCIEDAD_UR])),CALCULATE(AVERAGE(UnPivotted[FieldValue]),filter(UnPivotted,FILTERS(MAESTRO[SOCIEDAD_UR])=UnPivotted[SOCIEDAD_UR]&&UnPivotted[FieldValue]<>BLANK()))

does not work in all cases, when some values are missing it doesen't show the % and in some others it does. 

Anyways many thanks again because yours is the correct path to find the solution and the problem may be as I mentioned in a relation 

Regards

Borja 

[Borjaveigasvh]

Thanks so much, I'll start to implement this solution.

Sorry for the delay in answering.

Best 

Borja