In Power Bi, how to create a DAX Measure based on if statement from different aggregations-CodePudding

I have a problem calculating the total in a matrix visualization using a measure with an if statement. I created this simplified model to ask this doubt.

The goal is to create a measure that:

offers a 50% discount on books if customers bought more than 40% regarding the previous year and no discount otherwise
offers a 20% discount on the other products (pens and pencils) if customers bought more than 30% regarding the previous year and no discount otherwise
even if the above is fulfilled for some products, there will be no discount if in the sum of all the products the buyer didn't buy more than 10% compared to the previous year

I send the screen of the matrix summarizing what I should achieve:

Sample data:

What could I add to the measure created to get "0" instead of "3000" in the register highlighted?

CodePudding user response：

Seems works in a correct way. Please, note you have some wrong results in a result sample for example for c-pen 6500/5001-1=0.2997 it's less then 30. Also then 11501*0.2=2300.2 and you calculates 3450. Also, the measure calculates for - as more then 40% for books and more then 20% for other, as you asks. So, if you will have yoy = 0.4 then a discount is 0% if 0.401 then = 50% for books. You can adjust the measure as >=0.4 if you meant this.

Discount = 
VAR totalYOY = 
    CALCULATE(
        [sales_yoy_2]
        ,ALLSELECTED('base_2'[product])
    )
VAR allProductsWithinGroup =
        CALCULATETABLE(
            SUMMARIZE(
                'base_2'
                ,base_2[customer]
                ,base_2[product]
            )
            ,ALLSELECTED(base_2[product])
        )
VAR allProductsWithinGroupYOY =
        ADDCOLUMNS(
            allProductsWithinGroup
            ,"yoy",[sales_yoy_2]
            ,"salesTotal",[sales_total_2]
            ,"customerYOY", CALCULATE(
                                [sales_yoy_2]
                                ,ALLSELECTED('base_2'[product])
                            )
        )
VAR withDiscount = 
        ADDCOLUMNS(
            allProductsWithinGroupYOY
            ,"Discount", SWITCH(
                            TRUE()
                            ,[customerYOY]<0.1,0
                            ,[product]="book" && [yoy]>0.4,0.5*[salesTotal]
                            ,(NOT [product]="book") && [yoy]>0.3,0.2*[salesTotal]
                            ,0
                        )
        )
VAR result =
        FILTER(
            withDiscount
            ,[product] in VALUES(base_2[product])
        )
RETURN
    SUMX(result,[Discount])