Conditional measure to calculate

[Oros]

Hello,

I am trying to add a measure based on condition.  

If the Weight/Box=Default then the Total Boxes Ordered = Order

If the Weight/Box is NOT DEFAULT, then the Total Boxes Ordered = Order divided by Weight per Box.

Thanks.

[parry2k]

@Oros something is not right, you need to share pbix file to look into it otherwise something like this simple shouldn't take this long.

[parry2k]

@Oros can you share your expression? I asked this previously, if you cannot follow a simple request, not sure how to help?

[Oros]

Hi @parry2k ,

Sorry about that.  I used the same expression that you recommended. Here is what I entered:

Also, I organized and added a column named DEFAULT to hopefully make it clearer.  Thanks.

[parry2k]

@Oros are you adding a column or a measure? Can you share your DAX expression? And also can you paste the text instead of these tiny images.

[Oros]

Hi @parry2k ,

I am adding a column.  I do not mind if the solution can be for a measure.  Thanks.

Here is the error:

DAX comparison operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[parry2k]

@Oros try this in DAX using calculated columns

Total Boxes Ordered = 
IF ( YourTable[Weight/Box] = "Default", YourTable[Order], DIVIDE ( YourTable[Order], VALUE ( YourTable[Weight/Box] ) ) 

The reason you want to use VALUE in divide because your Weight/Box column is of Text type and we need to convert it into value. Read mode about value function here VALUE function (DAX) - DAX | Microsoft Docs

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Oros]

Hi @parry2k,

Thank you for your reply.  It seems that it does not work.  Maybe I am missing something?

[selimovd · Accepted solution]

Hey @Oros ,

if you want to do it in Power Query then add a "Custom Color" and add the following formula:

if [Weight_Box] = "Default" then [Order] else [Order] / [Weight_Box]

If you want to solve that in the data model as calculated column try the following formula:

Total Boxes Ordered =
IF(
    myTable[Weight_Box] = "Default",
    myTable[Order],
    DIVIDE(
        myTable[Order],
        myTable[Weight_Box]
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Oros]

Hi @selimovd ,

The custom column solution seems to be dividing everything and not seeing the 'DEFAULT' (or TRUE).

The calculated column in the data model also did not work.  Maybe I am missing something?

Thanks again.

[selimovd]

Hey @Oros ,

can you send an actual screenshot from PowerQuery or Power BI and not from Excel?

Or can you share your file? This would be the best option.

Best regards

Denis

[Oros]

Hi @selimovd 

I organized and added a column called DEFAULT, hopefully to make it clear.

Thanks.

[selimovd]

@Oros This is not a screenshot from Power BI or Power Query. Like this I can't help you...

[Oros]

Hi @selimovd 

That is NOT a Power Bi screenshot.  That is a change from the original post.

Thanks.