Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
powerbignc
Helper I
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

 

DateModelColorDiscount
1/1/2020FordRed0.15
1/1/2020FordBlue0.25
1/1/2020FordGrey0.3
1/1/2020FordGreen0.4
1/1/2020GMCRed0.6
1/1/2020GMCBlue0.7
1/1/2020GMCGrey0.1
1/1/2020GMCGreen0.11
1/1/2020ToyotaRed0.5
1/1/2020ToyotaBlue0.01
1/1/2020ToyotaGrey0.03
1/1/2020ToyotaGreen0.08
1/7/2020FordRed0.08
1/7/2020FordBlue0.16
1/7/2020FordGrey0.18
1/7/2020FordGreen0.19
1/7/2020GMCRed0.2
1/7/2020GMCBlue0.7
1/7/2020GMCGrey0.16
1/7/2020GMCGreen0.23
1/7/2020ToyotaRed0.24
1/7/2020ToyotaBlue0.16
1/7/2020ToyotaGrey0.46
1/7/2020ToyotaGreen0.12


Sales Data

DateModelSales PersonColorSale Amount
1/1/2020FordAS123Blue            30,000.00
1/1/2020FordAS124Grey            13,468.00
1/1/2020FordAS125Green            50,000.00
1/7/2020ToyotaAS126Red            10,000.00
1/7/2020FordAS127Blue            13,641.00
1/1/2020ToyotaAS128Red            19,000.00
1/1/2020ToyotaAS124Blue            18,000.00
1/1/2020ToyotaAS130Grey            46,000.00
1/7/2020FordAS126Grey            18,000.00
1/7/2020GMCAS125Green            13,461.00
1/8/2020ToyotaAS133Blue            16,000.00
1/9/2020GMCAS134Grey            65,000.00
1/10/2020GMCAS126Green          164,253.00
1/11/2020ToyotaAS136Red            13,000.00
1/12/2020FordAS137Blue            16,111.00
1/13/2020FordAS123Grey            13,520.00
1/14/2020ToyotaAS139Green            16,482.00



1 ACCEPTED SOLUTION
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1670555156084.png

 

 

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.

 

View solution in original post

4 REPLIES 4
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1670555156084.png

 

 

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
Helper I
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.

Bifinity_75
Solution Sage
Solution Sage

Hi @powerbignc , try this:

 

Insert a calculate column in your discount table:

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

Bifinity_75_0-1670441842451.png

 

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

Bifinity_75_1-1670441911153.png

 

I hope works for you, Best Regards

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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