Forum Discussion
Create a new column based on multiple conditions
Hi,
I need to create a new column called 'Score' based on the below example:
Condition: If Type = "V", Actual/Target % = 67% which is >=50 && <=75%, then give me V Score = 25, please look at the attached image.
Please Note: The 2 tables in the example are to be created manually in the Power BI, let me know if I should create 1 table or 2 separate tables to use the logic that you are going suggest.
Hi Anonymous
1. Create them as 2 different tables. Let me call the first table as Table1.
2. For the second Table I would create it with columns Min%, Max%, MScore, Vscore
eg 0,50,0,0
51,75,500,25
76,90,1000,200
91,100,2000,300
3. Now we want to add the Vscore from table2 as score in table1 based on the condition.
4. Crate a calculated column Vscore in Table1 with the expression
CALCULATE (VALUES ( Table2[Vscore] ),FILTER (Table2,Table1[Actual/Target%] >= Table2[Min%]&& Table1[Actual/Target%] <= Table2[Max%]))This will get the Vscore against each row value of Table1[Actual/Target%]
Cheers
CheenuSing
3 Replies
Hi Anonymous
1. Create them as 2 different tables. Let me call the first table as Table1.
2. For the second Table I would create it with columns Min%, Max%, MScore, Vscore
eg 0,50,0,0
51,75,500,25
76,90,1000,200
91,100,2000,300
3. Now we want to add the Vscore from table2 as score in table1 based on the condition.
4. Crate a calculated column Vscore in Table1 with the expression
CALCULATE (VALUES ( Table2[Vscore] ),FILTER (Table2,Table1[Actual/Target%] >= Table2[Min%]&& Table1[Actual/Target%] <= Table2[Max%]))This will get the Vscore against each row value of Table1[Actual/Target%]
Cheers
CheenuSing
Hi Anonymous ,
It worked like a charm. I have futher developed your logic to make it work in my case. Thanks a lot for your time on this.
Calc Column =
VAR VScore =
CALCULATE (
VALUES ( Table2[Vscore] ),
FILTER (
Table2,
Table1[Actual/Target%] >= Table2[Min%]
&& Table1[Actual/Target%] <= Table2[Max%]
)
)
VAR MScore =
CALCULATE (
VALUES ( Table2[Mscore] ),
FILTER (
Table2,
Table1[Actual/Target%] >= Table2[Min%]
&& Table1[Actual/Target%] <= Table2[Max%]
)
)
RETURN
IF ( Table1[Type] = "V", VScore, MScore )Cheers
Charan
Hi,
Share the base data in a format that can be pasted in MS Excel. Also, answer the following questions:
- If the Type is M then is there a seperate set of rules that will apply
- For row 3, why is the answer 200. For 34.70%, the result should be 0.
