topic Re: Calculate discount based on total salesamount in DAX Commands and Tips

Calculate discount based on total salesamount

Jonas_Holm — Mon, 04 Jul 2022 11:55:22 GMT

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?

Re: Calculate discount based on total salesamount

amitchandak — Mon, 04 Jul 2022 12:31:39 GMT

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

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

Re: Calculate discount based on total salesamount

Jonas_Holm — Mon, 04 Jul 2022 13:21:39 GMT

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?

Re: Calculate discount based on total salesamount

tamerj1 — Mon, 04 Jul 2022 13:44:35 GMT

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 ) ) )

Re: Calculate discount based on total salesamount

Jonas_Holm — Mon, 04 Jul 2022 15:14:47 GMT

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.

Re: Calculate discount based on total salesamount

tamerj1 — Mon, 04 Jul 2022 15:24:29 GMT

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

Re: Calculate discount based on total salesamount

Jonas_Holm — Mon, 04 Jul 2022 17:19:54 GMT

Hello tamerj1

This is the result for all of the measures:

Re: Calculate discount based on total salesamount

tamerj1 — Mon, 04 Jul 2022 17:58:17 GMT

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?

Re: Calculate discount based on total salesamount

tamerj1 — Mon, 04 Jul 2022 18:05:29 GMT

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 ) ) )

Re: Calculate discount based on total salesamount

Jonas_Holm — Mon, 04 Jul 2022 18:33:49 GMT

tamerj1

Here you go:

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

Re: Calculate discount based on total salesamount

tamerj1 — Mon, 04 Jul 2022 18:39:15 GMT

Jonas_Holm

Have you tried the last formula?

Re: Calculate discount based on total salesamount

Jonas_Holm — Mon, 04 Jul 2022 19:05:16 GMT

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.

Re: Calculate discount based on total salesamount

tamerj1 — Mon, 04 Jul 2022 19:12:08 GMT

Jonas_Holm

This one ☝️

Re: Calculate discount based on total salesamount

Jonas_Holm — Mon, 04 Jul 2022 19:18:48 GMT

Yes, gives the same result as the other one.

Re: Calculate discount based on total salesamount

Jonas_Holm — Mon, 11 Jul 2022 06:52:26 GMT

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