cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bmbhelper Frequent Visitor
Frequent Visitor

Calculate - Apply filter based on a IF condition

Hi Guys,

 

I don't know how to explain or title this. Let me give you my best shot.

 

I have a set of sales data, I want to create a measure that takes Qty * sale price per sku. Now the Issue is that the sale price changes every period.

 

How can I make a measure where the filter is dynamic according to the period inwhich the sale happened.

Something like this

Sales:=Qty times (sale price filtered by the sale's period price).

 

Does that make sense?

 

 

Raw Data   
CustomerProductPeriodQty
JohnProduct APeriod 120
JoeProduct BPeriod 21
GeoProduct APeriod 310
MaryProduct CPeriod 45
PatrickProduct CPeriod 58

 

 

Product Table   
ProductPeriod 1Period 2Period 3
Product A1.51.52
Product B1.51.552
Product C21.52

 

 

Thank you in advance!! 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate - Apply filter based on a IF condition

@bmbhelper,

 

Right click [Product] column from Product Table and select Unpivot Other Columns in Query Editor, then add a calculated column to Raw Data.

Price =
LOOKUPVALUE (
    'Product Table'[Value],
    'Product Table'[Product], 'Raw Data'[Product],
    'Product Table'[Attribute], 'Raw Data'[Period]
)
Measure =
SUMX ( 'Raw Data', 'Raw Data'[Qty] * 'Raw Data'[Price] )
Community Support Team _ Sam Zha
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

2 REPLIES 2
bmbhelper Frequent Visitor
Frequent Visitor

Re: Calculate - Apply filter based on a IF condition

 
Community Support Team
Community Support Team

Re: Calculate - Apply filter based on a IF condition

@bmbhelper,

 

Right click [Product] column from Product Table and select Unpivot Other Columns in Query Editor, then add a calculated column to Raw Data.

Price =
LOOKUPVALUE (
    'Product Table'[Value],
    'Product Table'[Product], 'Raw Data'[Product],
    'Product Table'[Attribute], 'Raw Data'[Period]
)
Measure =
SUMX ( 'Raw Data', 'Raw Data'[Qty] * 'Raw Data'[Price] )
Community Support Team _ Sam Zha
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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors