Calculation based on a set of rules

[setis]

Dear all,

I'm not sure how to approach this challenge. 

I have a table with a set of rules:

As you can see a case has a specific Fee_type when it meets all the conditions. 

F.Ex. A case is Easywhen the

ft_cause = A and

the case_type = Type_A and

the customer = CustomerA and

Activities <5  and

Nr calls <1.

I am looking for a Measure or calculated column in my cases table that gives me right price:

Any idea on how to approach this?

Thanks in advance

[amitchandak]

@setis , what is the logic for desired price2 =200 for easy.  Do you need this calculation on the aggerated number or at line level

[setis]

@amitchandak thanks for looking into this. 

I'm so sorry, there was a mistake in the table. I have just corrected it in the orogonal post. The easy price is 200.

The calculation I need is on a line level on the cases table. 

Thank in advance

[amitchandak]

@setis , can you provide sample data, sample rule data in table format

[setis]

@amitchandak Sure!

Sample rule

ft_name	ft_cause	Case_type	Customer	ft_operatorname	ft_number	Fee_type	Amount
Cause	ME		CustomerA			Difficult	1000
Case Type		Type_A	CustomerA			Difficult	1000
Customer			CustomerA			Difficult	1000
Activities			CustomerA	Less than	5	Easy	200
Cause	ME		CustomerA			Easy	200
Customer			CustomerA			Easy	200
Case Type		Type_A	CustomerA			Easy	200
Nr of calls			CustomerA	Less than	1	Easy	200

Sample data

CaseID	ft_cause	Case_type	Customer	Nr of calls	Nr Activities	Desired Result(Fee_type)	Desired Result2(price)
1221	ME	Type_A	CustomerA	0	4	Easy	200
1222	AA	Type_B	CustomerA	5		BLANK	BLANK
1223	ME	Type_A	CustomerA	0	6	Difficult	1000

[setis]

The weekend sank this post to the bottom of the forum. I'm bumping this to gain some monday morning visibility. 

I would really appreciate any help. 

[v-chuncz-msft · Accepted solution]

@setis 

You may try Pivot Column to shape data in Query Editor and then refer to this post.

[setis]

@v-chuncz-msft thanks for your answer. You are right. That's definitiely the way to go. 

I am having issues doing the unpivot in power query. Could you please help me with the steps to unpivot this correctly?

I have upload a sample here:  https://drive.google.com/file/d/16wKtLuvvUJ8gMMV5Hk0PYFiEcCR8oAYL/view?usp=sharing

What I'm trying to go is from here: 

To something like this:

At the same time, do  you have any idea on how to solve the cells Activities and Calls so I have something usable to calculate with?