Calculated ratio of discount rate based on maximum sales volume per month

[justine0812]

Hi,

I am trying to calculate a new table that will show me the "supposed to be discount rate per SKU" that should be offered to a wholesaler. The new discount rate is calculated by dividing the current sales volume, with the maximum sales volume per month per SKU and then multiplied with the lowest discount rate that we are offering on all wholesalers.

The tables look like this:

The expected discount rate is calculated by (=(C4/MAX(C4:F4)*MIN(C10:F10))) for C16 for example.

I'm quite struggling to show the expected discount rate I want. Hope someone is able to help me. 

Thanks a lot!!!

[v-yangliu-msft]

Hi  @justine0812 ,

You can try the following dax

Measure =
var _groupmonthsku=
MAXX(
    FILTER(ALL('Table'),    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&'Table'[SKU]=MAX('Table'[SKU])),[Sales])
var _mindiscountrate=
MINX(ALL('Table'),[Discount rate])
return
DIVIDE(
    MAX('Table'[Sales]) ,_groupmonthsku) * _mindiscountrate

Best Regards,

Liu Yang

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

[DallasBaba]

@justine0812 

If you want to translate this formula into DAX, create the a measure 

Expected Discount Rate =
DIVIDE(
SUM(Sales[Sales Volume]),
CALCULATE(MAX(Sales[Max Sales Volume]), ALL(Sales)),
0
) * MIN(Sales[Discount Rate])

===== Or

Expected Discount Rate = [Current Sales Volume] / MAXX('Table', [Current Sales Volume]) * MINX('Table', [Lowest Discount Rate])

Reference :
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-DAX-measure-for-Unit-Pric...

Kindly let me know if this works.

Thanks

[DallasBaba]

@justine0812  you need to correct your formula
(=(C4/MAX(C4:F4)*MIN(C10:F10))) into =(C4/MAX(C4:F4)*MIN(C10:F10)) .

I hope this helps, but if not let's try the following:

1 - Select cell C16.
2 - Copy the formula =(C4/MAX(C4:F4)*MIN(C10:F10)).
3 - Select the range of cells C16:F20.
4 - Paste the formula into the selected range of cells.

These will calculate the expected discount rate for each SKU in the table.

=====OR
Another option is to create a new column, "Expected Discount Rate," next to your existing table. In your example, it is in column G.

In cell G16 (or the corresponding cell for each SKU), enter this formula: =C16 / MAX(C4:F4) * MIN(C10:F10)

Based on your provided formula, these will calculate the expected discount rate for the SKU in row 16.

Let me know if this helps.

Thanks

[justine0812]

Hi,

Thanks for this! But I am wondering how this translates to a formula in DAX?

I am trying to calculate a new column using DAX but is a bit struggling to see what I want to show.

Thanks!