Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Borjaveigasvh
Regular Visitor

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 

1 ACCEPTED SOLUTION
JustJan
Responsive Resident
Responsive Resident

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
    )
    )
2020-07-24 01_11_53-Community - calcualted column from diferent columns - Power BI Desktop.png
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

View solution in original post

3 REPLIES 3
JustJan
Responsive Resident
Responsive Resident

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
    )
    )
2020-07-24 01_11_53-Community - calcualted column from diferent columns - Power BI Desktop.png
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

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 

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

Sorry for the delay in answering.

 

Best 

Borja 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors