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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
san_21
Frequent Visitor

Create a custom column

Hi,

I need help in creating custom column in PowerBi same as "Calculated MV" column of below excel screenshot.

If market value data is available in column B, it should pick up same data or else calculate MV based on ROR.

 

Formula used in excel for Calculated MV -

=IF(B3="",D2*(1+C3/100),B3)

 

san_21_0-1707828195636.png

 

1 ACCEPTED SOLUTION

@san_21 

sorry my bad, 

Daniel29195_0-1707899185531.png

 

create part_2 column : 

 

part_2 = 
1 + 'Table'[ROR]/ 100 

 

 

create mv column : 

 

mv = 
SWITCH(
    TRUE(),
    not ISBLANK('Table'[Market Value]) , 'Table'[Market Value],


    var date_with_lastnonblank_market_value =  
        sELECTCOLUMNS(
        LASTNONBLANK(
            FILTER(
                'Table',
                'Table'[Date]<=EARLIER('Table'[Date])
        ),
        'Table'[Market Value]
        ),'Table'[Date]
        )

    var part_2 = 
    SELECTCOLUMNS(
        WINDOW(
            0,
            ABS,
            0,
            REL ,
            FILTER(
             SUMMARIZE(
                'Table',
                 'Table'[Date],
                 'Table'[part_2]
            ) , 'Table'[Date] >=date_with_lastnonblank_market_value
            ),
            ORDERBY('Table'[Date],asc)),
        'Table'[part_2]
        )
    



    var market_value =  
    sELECTCOLUMNS(
    LASTNONBLANK(
        FILTER(
            'Table',
            'Table'[Date]<=EARLIER('Table'[Date])
    ),
    'Table'[Market Value]
    ),'Table'[Market Value]
    )


    var part_2_product =  PRODUCTX(part_2, [part_2])
    

    return    market_value * part_2_product

)

 

 

 

let me know if this helps 

 

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Wouldn't this be better as a measure (rather than a Custom Column formula)?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Yes measure will be better. Can you please help with query.

Hi,

PBI file attached.  This is a measure solution.

Hope this helps.

Ashish_Mathur_0-1708140151429.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

If it is a calculated column you can also do it in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5RDoAgDAPQu/BNpOsGzLMQ7n8N0aARE//WvLRZa0GYVBJBDXEEsR0YV+hxWLrEzhwDNgAZZRIXKqSoTdKFTKk1T7KFqjh9Sn6JbHR95sr3Cbk79Vd8WUPNPqQf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Market Value" = _t, ROR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Market Value", Currency.Type}, {"ROR", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Calculated MV", each 
[Market Value]??List.Accumulate({1..[Index]},#"Added Index"[Market Value]{0},(state,current)=>state*(1+#"Added Index"[ROR]{current}/100)),Currency.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Daniel29195
Super User
Super User

@san_21 

 

create a dax calculated column as follow :

 

cc = 
switch(
true() , 
NOT isblank(tbl_name[Market Value]) ,
  tbl_name[market value] ,
   tbl_name[ROR]
)  

 

you can achieve the same thing using conditional column in power query . 

Daniel29195_0-1707864587379.png

 

keep nb 4 empty, -->  this means null . 

so if column name -->  market value --> equals --> empty textbox -->  select column --> choose ROR column, 

else choose Market value column , 

 

 

let me know if this helps .

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

 

Hi Daniel,

Thanks for your solution.

 

With the help of DAX formula you provided, I m getting Market Value and ROR data in one column. 

However I want Derived market value data by multiplying ROR with previous Market Value.

In column B, Market value are missing for non-month end date, so I want to calculate those by multiplying previous market value with ROR.

 

Can you please help me with DAX custom column / measure?

@san_21 

sorry my bad, 

Daniel29195_0-1707899185531.png

 

create part_2 column : 

 

part_2 = 
1 + 'Table'[ROR]/ 100 

 

 

create mv column : 

 

mv = 
SWITCH(
    TRUE(),
    not ISBLANK('Table'[Market Value]) , 'Table'[Market Value],


    var date_with_lastnonblank_market_value =  
        sELECTCOLUMNS(
        LASTNONBLANK(
            FILTER(
                'Table',
                'Table'[Date]<=EARLIER('Table'[Date])
        ),
        'Table'[Market Value]
        ),'Table'[Date]
        )

    var part_2 = 
    SELECTCOLUMNS(
        WINDOW(
            0,
            ABS,
            0,
            REL ,
            FILTER(
             SUMMARIZE(
                'Table',
                 'Table'[Date],
                 'Table'[part_2]
            ) , 'Table'[Date] >=date_with_lastnonblank_market_value
            ),
            ORDERBY('Table'[Date],asc)),
        'Table'[part_2]
        )
    



    var market_value =  
    sELECTCOLUMNS(
    LASTNONBLANK(
        FILTER(
            'Table',
            'Table'[Date]<=EARLIER('Table'[Date])
    ),
    'Table'[Market Value]
    ),'Table'[Market Value]
    )


    var part_2_product =  PRODUCTX(part_2, [part_2])
    

    return    market_value * part_2_product

)

 

 

 

let me know if this helps 

 

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

@san_21 and sorry for the late response 

i thought i did click on the reply button . 

Thanks Daniel for helping me with query.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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