Identify values on the basis of coupons and mapped it in that field

[Anonymous]

Hi all,

I got an issue that I need to resolve inside Power BI, I want to know if there are any mechanisms that 1) detects all coupons codes on the basis of "item promotion discount" and "item price", 2) checks where we have a value in an "item promotion discount" field and then 3) fills the blank fields inside "item promotion discount" with the correct value. 
some coupons are giving fixed price discount and others are giving % discount, So, any possibility to check which are the fixed and which ones are % and then add the right values in an empty "item promotion discount" column on the basis of coupons and item price?

There are two tables i used to get this data and these tables have a many-to-many relationship.

id	item price	item promotion discount	Coupons
11529	€ 144.95	€ 7.00	VPC-4-27921144 Coupon
54074	€ 159.95	€ 7.00	VPC-4-27921144 Coupon
11523	€ 160.10		VPC-4-27921144 Coupon
69128	€ 104.95	€ 10.00	VPC-4-27921303 Coupon
16300	€ 104.95		VPC-4-27921303 Coupon
122609	€ 9.90	€ 1.49	VPC-4-35613647 Coupon
228375	€ 15.90	€ 0.92	VPC-4-35613647 Coupon
107221	€ 31.80		VPC-4-35613647 Coupon
13283	€ 24.90	€ 2.49	VPC-5-27921929 Coupon
42634	€ 29.90	€ 2.99	VPC-5-27921929 Coupon
790065	€ 29.90		VPC-5-27921929 Coupon
790065	€ 24.90		VPC-5-27921929 Coupon
790047	€ 24.90	€ 2.49	VPC-5-27921929 Coupon

I can use Lookup functionality but this is not only a mapping issue, i have to identify the value of the right coupons too. 
Anyone who can help me here?   @amitchandak 

Thanks,

Ahsan

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I created these two new columns and my calculation logic is that if each group of coupons has the same discount, then the coupon is a fixed value. If the coupon has 2 or more different discount, then it is a percentage discount. Note that because the rows marked with red lines have different discount %, I took the maximum discount % for the calculation.

discount% = [item promotion discount] / [item price]

promotion discount = 
VAR IsPercentagesorFixed =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[item promotion discount] ),
        ALLEXCEPT ( 'Table', 'Table'[Coupons] ),
        'Table'[item promotion discount] <> BLANK ()
    )
VAR AveDiscount =
    MAXX (
        FILTER (
            'Table',
            'Table'[Coupons] = EARLIER ( 'Table'[Coupons] )
                && [discount%] <> BLANK ()
        ),
        [discount%]
    )
RETURN
    IF (
        IsPercentagesorFixed = 1,
        CALCULATE (
            MAX ( 'Table'[item promotion discount] ),
            ALLEXCEPT ( 'Table', 'Table'[Coupons] )
        ),
        AveDiscount * [item price]
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

would you mind posting the second table as well?