topic Re: Problem creating a calcualted column from diferent columns, Help needed in DAX Commands and Tips

Problem creating a calcualted column from diferent columns, Help needed

Borjaveigasvh — Thu, 23 Jul 2020 10:04:48 GMT

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

Re: Problem creating a calcualted column from diferent columns, Help needed

Anonymous — Thu, 23 Jul 2020 23:17:07 GMT

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

Re: Problem creating a calcualted column from diferent columns, Help needed

Borjaveigasvh — Tue, 28 Jul 2020 16:10:15 GMT

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

Sorry for the delay in answering.

Best

Borja

Re: Problem creating a calcualted column from diferent columns, Help needed

Borjaveigasvh — Tue, 18 Aug 2020 09:23:22 GMT

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