Need formula to do lookup value to dimension value table and come up with net amount

[powerbignc]

I have two tables. A Sales table and a discount table where I apply a discount by day, Car Model and color. The sales data table is my main fact table and is at a lower level of detail obviously. Below is a sample of the two tables. I am trying to find a way to build a DAX measure that does a look up across the sales table and matches the sales detail where Date, Model and Color are a match and returns the discount amount for that row to be used in a calculation for Net Sales amount.

For example on row one of the sales data table, the sale amount I see was for 30,000, but I need to be able to reference the discount amount and calculate a new NET Sales amount by referencing the dicount table. For Row 1, the discount would be 0.25, since on 1/1/2020 for Blue Fords the discount was 0.25. SO if the sale was 30,000 and a discount of 0.25 was applied the Net Sale would be 30000-(30000*0.25)= $22,500.

I don't have the ability to join these tables together in Power Query due to table size and other restrictions, but need the ability to write dax that will calculate this properly. How do I build this Net Sales Dax Measure given the two tables are at different levels of detail?

Discount Dimension Table by Date/Model/Color

Date	Model	Color	Discount
1/1/2020	Ford	Red	0.15
1/1/2020	Ford	Blue	0.25
1/1/2020	Ford	Grey	0.3
1/1/2020	Ford	Green	0.4
1/1/2020	GMC	Red	0.6
1/1/2020	GMC	Blue	0.7
1/1/2020	GMC	Grey	0.1
1/1/2020	GMC	Green	0.11
1/1/2020	Toyota	Red	0.5
1/1/2020	Toyota	Blue	0.01
1/1/2020	Toyota	Grey	0.03
1/1/2020	Toyota	Green	0.08
1/7/2020	Ford	Red	0.08
1/7/2020	Ford	Blue	0.16
1/7/2020	Ford	Grey	0.18
1/7/2020	Ford	Green	0.19
1/7/2020	GMC	Red	0.2
1/7/2020	GMC	Blue	0.7
1/7/2020	GMC	Grey	0.16
1/7/2020	GMC	Green	0.23
1/7/2020	Toyota	Red	0.24
1/7/2020	Toyota	Blue	0.16
1/7/2020	Toyota	Grey	0.46
1/7/2020	Toyota	Green	0.12

Sales Data

Date	Model	Sales Person	Color	Sale Amount
1/1/2020	Ford	AS123	Blue	30,000.00
1/1/2020	Ford	AS124	Grey	13,468.00
1/1/2020	Ford	AS125	Green	50,000.00
1/7/2020	Toyota	AS126	Red	10,000.00
1/7/2020	Ford	AS127	Blue	13,641.00
1/1/2020	Toyota	AS128	Red	19,000.00
1/1/2020	Toyota	AS124	Blue	18,000.00
1/1/2020	Toyota	AS130	Grey	46,000.00
1/7/2020	Ford	AS126	Grey	18,000.00
1/7/2020	GMC	AS125	Green	13,461.00
1/8/2020	Toyota	AS133	Blue	16,000.00
1/9/2020	GMC	AS134	Grey	65,000.00
1/10/2020	GMC	AS126	Green	164,253.00
1/11/2020	Toyota	AS136	Red	13,000.00
1/12/2020	Ford	AS137	Blue	16,111.00
1/13/2020	Ford	AS123	Grey	13,520.00
1/14/2020	Toyota	AS139	Green	16,482.00

[v-jialluo-msft · Accepted solution]

Hi  @powerbignc ,

Please follow these steps:
(1) Create a new measure

Net Sales = 
VAR _M = MAX(Sales[Model])
VAR _C = MAX(Sales[Color])
VAR _D = MAX(Sales[Date])
VAR _DISCOUNT = CALCULATE(VALUES(Discount[Discount]),FILTER(ALL(Discount),Discount[Color] = _C && Discount[Date] = _D &&Discount[Model] = _M))
RETURN MAX(Sales[Sale Amount])*(1-_DISCOUNT)

(2)Final output

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

[powerbignc]

Is there a way to do this without a calculated column? I won't have the ability to change the table structure to add calculated columns and am trying to do this 100% within the DAX measure.

[Bifinity_75]

Hi @powerbignc , try this:

Insert a calculate column in your discount table:

Column = Table1[Date]&Table1[Color]&Table1[Model]

- In your sales table insert this calculate column:

Net Price = 
    var discount_= maxx(filter(Table1 , search(Table1[Column],Table2[Date]&Table2[Color]&Table2[Model],,0)>0),Table1[Discount])
        return
    Table2[Sale Amount]-(Table2[Sale Amount]*discount_)

The result:

I hope works for you, Best Regards

[Greg_Deckler]

@powerbignc You can use LOOKUPVALUE or MAXX(FILTER(...),...)