Forum Discussion

Borjaveigasvh's avatar
Borjaveigasvh
Regular Visitor
6 years ago
Solved

Problem creating a calcualted column from diferent columns, Help needed

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 

average
columns
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Borjaveigasvh
      Regular Visitor
      6 years ago

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

      Sorry for the delay in answering.

       

      Best 

      Borja 

    • Borjaveigasvh's avatar
      Borjaveigasvh
      Regular Visitor
      6 years ago

      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