Grouping based on conditions (segmentation)

[Anonymous]

Hi all,

I was looking online for conditional formatting, but mostly people use it to color a certain cell in there table.

But I want to use a few conditions to calculate a bonus. The bonus is as followed:

If value is lower than 8500, bonus is 0

If value is higher than 8500 and lower than 9000, bonus is 100

If value is higher than 9000, bonus is 150

To clarify, I have an excel file with the bonus min/max criteria and the value comes from another source.

Here's a picture to visualize it.

 

So I want to create a virtual table with the bonus amount based on the conditional formatting. 

EDIT: So far I'm using Countrows to create the virtual table. For now I only have 1 condition in this measure. But I'm not sure how to return specific values when it falls under a condition.

Bonus = 
COUNTROWS (
    FILTER( blad1, Blad1[Value] >= 9000 ) )

For example, here I would want to return 150.

But I also want < 8500 to return 0 and >= 8500 AND < 9000 to return 100. All in the same column. But is there a way to return a specific value? Or I'm I going wrong about this..

Thanks in advance!

[Anonymous]

Hi @Pragati11 

I'm still having a problem. I'm using this measrement:

BonusTest = COALESCE(IF(SUM(Income[Value]) < SUM('BonusValues'[BonusMin]),0), IF(SUM(Income[Value]) < SUM('BonusValues'[BonusMax]), SUM('BonusValues'[BonusValueMin])), SUM('BonusValues'[BonusValueMax]))

But somehow, it's not adding everything up correctly. The conditions are working fine.

But as you can see, the "total" value is incorrect. When I insert this function into a Calculated column, the total value is correct, but the conditions aren't working.. Can someone help me out on how to have the conditions working, as well as the total value?

[Pragati11]

Hi @Anonymous ,

You mentioned earlier that the last solution worked with calculated column as well as the measure that you came up with.

Now what is changed that it's not working? You current DAX is using COALESCE. Can you mention why this DAX is changed or is there any other requirement now?

Thanks,

Pragati

[Anonymous]

Hi, I looked at your solution some more, but in the future it's possible that the conditions or bonus values change. That's why I want to use for example an Excel sheet with these values. So that anyone can easily change the values, if needed. Currently I'm using this solution in a calculated column, and it seems to be working.. I changed the coalsece with calculate:

BonusC = CALCULATE(
        (if(sum(Income[Value])
        <sum('BonusValues'[BonusMin]),0, CALCULATE(if(sum(Omzetten[Value])
        <sum('BonusValues'[BonusMax]),sum('BonusValues'[BonusValueMin]),sum('BonusValues'[BonusValueMax]))))))

I think this is an good solution. Sorry for ressurecting this post, I just found this out.. Do you see any improvements? This is a calculated Column and Parry mentioned that measures are better. Is this the same in this case? Or is a calculated column fine like this?

[Pragati11]

Hi @Anonymous ,

If you use the above exactly same DAX as a measure, it should work. Did you try it as a measure?

[Anonymous]

This is the result when I use it as a measure:

The condition are working, but the total value is incorrect. 

[Pragati11]

Hi @Anonymous ,

Just refer this article for incorrect totals while using measure. This shows a way of making the totals right in a measure calculation.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Thanks,

Pragati

[Anonymous]

@Pragati11 I want to expand my current function because there is another requirement. Would it be more appropiate to open a new thread? Since this one is about the conditional segmentation, which is now completed? 

[Pragati11]

Hi @Anonymous ,

Yes open a new thread. This will enable others as well to share their views.

Thanks,

Pragati

[Anonymous]

If I understand correctly, I should wrap my measure in HASONEFILTER ? The only thing I'm not sure in my case is, which column do I select? Because the measure itself is showing incorrect total value.. 

[parry2k]

@Anonymous sorry for jumping in here. I would avoid creating calculated columns where possible and try to create measures. Here is a amazing blog post talks about the difference between both.

Cheers!!

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

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.⚡

[Anonymous]

Hi, I managed to use a measure:

BonusC = CALCULATE(
        (if(sum(Income[Value])
        <sum('BonusValues'[BonusMin]),0, CALCULATE(if(sum(Income[Value])
        <sum('BonusValues'[BonusMax]),sum('BonusValues'[BonusValueMin]),sum('BonusValues'[BonusValueMax]))))))

But it's not giving the correct total amount. Any reason why?

[Anonymous]

Thanks for the tip, I'll look into it. Problem is, I could not get it to work using a measure..

[Pragati11 · Accepted solution]

Hi @Anonymous ,

You can achieve this by writing a conditional DAX IF statement. Try creating a calculated column as follows:

Bonus Calc = IF(tablename[Value] < 8500, 0, IF(tablename[Value] > 8500 && tablename[Value]  < 9000, 100, 150))

Try the above DAX and replace "tablename" with your table name in the baove formula.

Thanks,

Pragati

[Anonymous]

When I do that I get an error saying it can't find the column "Value" in the table "Bonus".. I tried something simular and had the same problem. It can only find another measure inside that Table..

This is the error:

A single value for the column 'Value' in the table 'Bonus' cannot be determined. This can occur if the measure's formula references a column that contains many values and no aggregation is specified as minimum, maximum, number, or sum to produce a single result.

This is the table:

And this is the measure you suggested

[Pragati11]

Hi @Anonymous ,

You are getting that error because you are creating a measure. I mentioned in my response that create a calculated column.

"Value" column I took in my DAX based on your screesnshot. Try replacing it with you actual column name in the data.

Thanks,

Pragati

[Anonymous]

Thanks a lot.. In a lot of online video's people are using measures for this purpose.. But what's the difference between a calculated column and a measure? I was trying the same with a measure but kept having that problem..