create column or measure to Sum the number of used coupon based on type

[jnn4282]

https://we.tl/t-SE8z9hLbj7

I would like to request help.

The attached date is my file. We give coupons to the registered guests from time to time.

As a data type is remarked as give or use for analysis.

Suppose for the month of January how many BD coupon was used? can be calculated as

BD(Use） = calculate(Distinctcount('table'[CustomerID]),(Table[CouponName]=BD),Table[CouponType] ="use")

I want to calculate estimated sales(assumption) that if a customer uses the BD coupon they spend around 15 dollars if the Lunch coupon is used spend 20 dollars and the appetizer  10 dollars.

Are there any Hints!

Your support will be highly appreciated.

Result expected will be if 10 BD coupons are used expected sales will be 150 dollors for entire months.

[Anonymous · Accepted solution]

Hi @jnn4282 ,

If you want to calculate the total used coupon of each CustomerID, each Coupon Name and each YearMonth , please try:

Coupon Count =
CALCULATE (
    COUNTROWS ( 'テーブル2' ),
    FILTER (
        'テーブル2',
        [CustomerID] = MAX ( 'テーブル2'[CustomerID] )
            && [coupon Name] = MAX ( 'テーブル2'[coupon Name] )
            && YEAR ( 'テーブル2'[UsedDate] ) = YEAR ( MAX ( 'テーブル2'[UsedDate] ) )
            && MONTH ( 'テーブル2'[UsedDate] ) = MONTH ( MAX ( 'テーブル2'[UsedDate] ) )
            && [coupon Type] = "use"
    )
)

Total Coupon =
SWITCH (
    MAX ( 'テーブル2'[coupon Name] ),
    "BD", 15,
    "Lunch", 20,
    "Appetizer", 10,
    "Drink", 5
) * [Coupon Count]

Output:

Or if you are still confused about it, please provide me with more details about your expected output to help us clarify your scenario.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnn4282]

@Anonymous 

I appreciate your help. Your explanation was very easy to understand.
I am weak on nesting the function. You made my day.

Thank you so much.