cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

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

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

1 ACCEPTED SOLUTION
Community Support

Hi  @powerbignc ,

(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.

4 REPLIES 4
Community Support

Hi  @powerbignc ,

(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.

Helper I

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.

Solution Sage

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors