Calculate discount based on total salesamount

[Jonas_Holm]

Hi everyone,

I have a problem, where I can't get the calculation of the Row "RABAT" (Discount)" calculated to a percentage of SalesAmount, and then afterwards to be subtracted from SalesAmount.

In this specific case, I want the:

63,35 / 1.267 = 5% = Discount ("RABAT")

294 * (1-5%) = 279,3

237 * (1-5%) = 225,15

191,2 * (1-5%) = 181,64

162 * (1-5%) = 153,9

156 * (1-5%) = 148,2

149,4 * (1-5%) = 141,93

77,4 * (1-5%) = 73,53

Total SalesAmount = 1.203,65

I have tried with this measure:

SalesAmount = CALCULATE(SUM('Værdiposter'[Salgsbeløb (faktisk)]),

FILTER(ALL('Værdiposter'[Varenr.]),'Værdiposter'[Varenr.]="RABAT"))*-1 / Salesamount. But just ends up with infinity.

Can someone help me out?

[tamerj1]

@Jonas_Holm 

Meanwhile please also try

SalesAmount =
SUMX (
    SUMMARIZE ( 'Værdiposter', 'Værdiposter'[Bilagsnr.], 'Værdiposter'[Varenr.] ),
    CALCULATE (
        VAR CurrentVarenr =
            SELECTEDVALUE ( 'Værdiposter'[Varenr.] )
        VAR CurrentAmont =
            IF (
                CurrentVarenr IN { "RABAT", "FRAGT", "KAMPAGNE" },
                0,
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] )
            )
        VAR DiscountAmount =
            CALCULATE (
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] ),
                'Værdiposter'[Varenr.] = "RABAT",
                ALLEXCEPT ( 'Værdiposter', 'Værdiposter'[Bilagsnr.] )
            )
        VAR TotalAmount =
            CALCULATE (
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] ),
                FILTER (
                    'Værdiposter',
                    NOT ( 'Værdiposter'[Varenr.] IN { "RABAT", "FRAGT", "KAMPAGNE" } )
                ),
                VALUES ( 'Værdiposter'[Bilagsnr.] ),
                REMOVEFILTERS ()
            )
        VAR DiscountPercentage =
            DIVIDE ( DiscountAmount, TotalAmount )
        RETURN
            CurrentAmont * ( 1 + DiscountPercentage )
    )
)

[tamerj1]

@Jonas_Holm 

This one ☝️ 

[Jonas_Holm]

@tamerj1 maybe you want to give it another shot? Because the last formula weren't giving the correct output.

[Jonas_Holm]

Yes, gives the same result as the other one.

[tamerj1]

@Jonas_Holm 
Please try

SalesAmount =
SUMX (
    SUMMARIZE ( 'Værdiposter', 'Værdiposter'[Bilagsnr.], 'Værdiposter'[Varenr.] ),
    CALCULATE (
        VAR CurrentVarenr =
            SELECTEDVALUE ( 'Værdiposter'[Varenr.] )
        VAR CurrentAmont =
            IF (
                CurrentVarenr IN { "RABAT", "FRAGT", "KAMPAGNE" },
                0,
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] )
            )
        VAR DiscountAmount =
            CALCULATE (
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] ),
                'Værdiposter'[Varenr.] = "RABAT",
                ALLEXCEPT ( 'Værdiposter', 'Værdiposter'[Bilagsnr.] )
            )
        VAR TotalAmount =
            CALCULATE (
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] ),
                FILTER (
                    'Værdiposter',
                    NOT ( 'Værdiposter'[Varenr.] IN { "RABAT", "FRAGT", "KAMPAGNE" } )
                ),
                ALLEXCEPT ( 'Værdiposter', 'Værdiposter'[Bilagsnr.] )
            )
        VAR DiscountPercentage =
            DIVIDE ( DiscountAmount, TotalAmount )
        RETURN
            CurrentAmont * ( 1 + DiscountPercentage )
    )
)

[Jonas_Holm]

Hello @tamerj1 

The result is getting closer to the prefered result.

But the discount should only be applied to the "Varenr. RABAT" - which in this case is 5%. But I cannot see, what I should re-do in your formula, to get this to work as intended. Hope you can help me further.

 this is the prefered result I want to receive.

[tamerj1]

@Jonas_Holm 

Ok I will into it tomorrow morning. Actually I don't have data to work with I only typed the code on my phone. Please help me out by returning the variables and check what values you get. Thank you

[Jonas_Holm]

Hello @tamerj1 

This is the result for all of the measures:

[tamerj1]

@Jonas_Holm 

this great thank you. It is clear noe thaere a filter comming from another table? Please share a screenshot or your data model. From which tables are the columns involved in this table visual?

[Jonas_Holm]

@tamerj1 

Here you go:

But we are only using VÆRDIPOSTER as a table and then the date relation obv.

[tamerj1]

@Jonas_Holm 

Have you tried the last formula?

[Jonas_Holm]

this one, because I have tried that.

SalesAmount =
SUMX (
    SUMMARIZE ( 'Værdiposter', 'Værdiposter'[Bilagsnr.], 'Værdiposter'[Varenr.] ),
    CALCULATE (
        VAR CurrentVarenr =
            SELECTEDVALUE ( 'Værdiposter'[Varenr.] )
        VAR CurrentAmont =
            IF (
                CurrentVarenr IN { "RABAT", "FRAGT", "KAMPAGNE" },
                0,
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] )
            )
        VAR DiscountAmount =
            CALCULATE (
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] ),
                'Værdiposter'[Varenr.] = "RABAT",
                ALLEXCEPT ( 'Værdiposter', 'Værdiposter'[Bilagsnr.] )
            )
        VAR TotalAmount =
            CALCULATE (
                SUM ( 'Værdiposter'[Salgsbeløb (faktisk)] ),
                FILTER (
                    'Værdiposter',
                    NOT ( 'Værdiposter'[Varenr.] IN { "RABAT", "FRAGT", "KAMPAGNE" } )
                ),
                ALLEXCEPT ( 'Værdiposter', 'Værdiposter'[Bilagsnr.] )
            )
        VAR DiscountPercentage =
            DIVIDE ( DiscountAmount, TotalAmount )
        RETURN
            CurrentAmont * ( 1 + DiscountPercentage )
    )
)

I had also tried the one with removefilters - same result.

[amitchandak]

@Jonas_Holm , Now very clear. But if this discount %

CALCULATE(SUM('Værdiposter'[Salgsbeløb (faktisk)]),
FILTER(ALL('Værdiposter'[Varenr.]),'Værdiposter'[Varenr.]="RABAT"))

Then sales value is 

(1- CALCULATE(SUM('Værdiposter'[Salgsbeløb (faktisk)]),
FILTER(ALL('Værdiposter'[Varenr.]),'Værdiposter'[Varenr.]="RABAT")) ) * Salesamount

or

sumx( 'Værdiposter' , (1- CALCULATE(SUM('Værdiposter'[Salgsbeløb (faktisk)]),
FILTER(ALL('Værdiposter'[Varenr.]),'Værdiposter'[Varenr.]="RABAT")) ) * Salesamount )

[Jonas_Holm]

@amitchandak maybe I wasn't totally clear, but the way you made the calculations doesn't seem to work.

I have made this formula from SumX

SalesAmountNew =

(SUMX('Værdiposter',

(1 - CALCULATE(SUM('Værdiposter'[Salgsbeløb (faktisk)]),

FILTER(ALL('Værdiposter'[Varenr.]),'Værdiposter'[Varenr.]="RABAT")))

* [Sales Amount]))

And sales amount is: 

Sales Amount =

SUM('Værdiposter'[Salgsbeløb (faktisk)]) - [Discount] - [Kampagne]

 I even tried to make the sumx calculation:

(SUMX('Værdiposter',

(1 - CALCULATE(SUM('Værdiposter'[Salgsbeløb (faktisk)]),

FILTER(ALL('Værdiposter'[Varenr.]),'Værdiposter'[Varenr.]="RABAT")))

* [Discount %]))

But that didn't seem to work either.

Do you have another idea, on how to calculate the discount % and use that for each row?