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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ganesh575
Frequent Visitor

Average margin of filtered products using either RegPrice or SalePrice depending on date.

Hi everyone!

 

So I have a product catalog table tht looks like this:

 

Prods      Categories     ProdCost     RegPrice     SalePrice     SaleStartDate     SaleEndDate    RegMargin%     SaleMargin%

Prod 1     Category A

Prod 2     Category A

Prod 3     Category A

Prod 4     Category B

Prod 5     Category B

Prod 6     Category C

 

(Calculated column) RegMargin% = 1-(divide(ProdCost,RegPrice))

This margin stays the same irrespective of date since regular prices don't fluctuate.

 

(Calculated column) SaleMargin% = 1-(divide(ProdCost,SalePrice))

This margin would be lower than RegMargin%, but would only apply on dates when a product is on sale.

 

I need to be able to compute the correct "daily" average margin of multiple products/categories (based on slicers/filters) for each date on my calendar table... For each calendar date, the formula should check if each product is on sale on that date (SaleStartDate < calendar date < SaleEndDate) ... IF the product IS on sale, it should use the SaleMargin% of that product as part of its average calculation. If the product IS NOT on sale, it should use the RegMargin% as part of its average calculation.

 

Is there any way to create this type of "dynamic average" that can be used with slicers/filters? Would this be a KPI?

 

Please help, thank you!!!

1 ACCEPTED SOLUTION
14 REPLIES 14
Ashish_Mathur
Super User
Super User

Hi,

 

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
BILASolution
Solution Specialist
Solution Specialist

Hi @ganesh575

 

Try this measures...

 

Total ProdCost = SUM('Product'[ProdCost]) 
Total RegPrice = SUM('Product'[RegPrice]) 
Total SalePrice = SUM('Product'[SalePrice]) 
%RegMargin = 1 - DIVIDE([Total ProdCost];[Total RegPrice];0) 
%SaleMargin = 1 - DIVIDE([Total ProdCost];[Total SalePrice];0) 
% Margin = 

var d = SELECTEDVALUE('Calendar'[Date])
var prod = SELECTEDVALUE('Product'[Prods])
var startdate = LOOKUPVALUE('Product'[SaleStartDate];'Product'[Prods];prod)
var enddate = LOOKUPVALUE('Product'[SaleEndDate];'Product'[Prods];prod)
return
IF(d >= startdate && d <= enddate ; [%SaleMargin];[%RegMargin])  

 

I hope it helps

BILASolution

Thanks BILASolution!

 

Can I put this whole thing into 1 measure? since I just need the end result i.e. % margin... Or do I need 1 column for each of them?

Sure, use this measure...

 

% Margin 2 = 
 
var d = SELECTEDVALUE('Calendar'[Date])
var prod = SELECTEDVALUE('Product'[Prods])
var startdate = LOOKUPVALUE('Product'[SaleStartDate];'Product'[Prods];prod)
var endadate = LOOKUPVALUE('Product'[SaleEndDate];'Product'[Prods];prod)

return

IF
(
    d >= startdate && d <= endadate ;  
    1 - DIVIDE(SUM('Product'[ProdCost]);SUM('Product'[SalePrice]);0);
    1 - DIVIDE(SUM('Product'[ProdCost]);SUM('Product'[RegPrice]);1)
)  

BILASolution

This looks promising!!! BUT when I add the measure as a column to my table, it is stuck on calculating, and doesnt load.

 

How does it know what var d and var prod is... Do I just add a slicer for calendar date and prod ?

 

And what happens is more than one value is selected for calendar date and prod ?

 

 

 

Thank you so much for your time by the way.

 

I created the measures thinking in the next funcionality...

 

g3.png

 

 

 

The limitation is that you only can select one product and one day. I think, selecting more than one product could be problematic, because of the SaleStartDate and the SalesEndDate (They're distincts for each product). Or maybe exists some business rule about that I don't know.

 

Your other question was convert the measure to a calculated column, that is not possible due to the date and product filters, the calculated columns couldn't be dynamics. I need more information.

 

Would you explain me with some graphs or a demo report what's exactly you are looking for?

 

BILASolution

Hey BILASolution and Ashish,

 

So I've put a sample product data set together so I can explain the end result I'm looking for.

 

--------------------------------------------------

 

2018-02-01.png

 

The above is a sample that represents our 'Products' table in Power BI. SaleStartDate and SaleEndDate both have a many-to-1 relationship with the Date column of a separate 'Calendar' table. (These sales are pre-planned for the year)

 

Using this table, I need a dashboard page with a visual that shows AVG % MARGIN, and 3 slicers:

  1. 'Calendar' Date - Single Select
  2. 'Products' Category - Multi-Select
  3. 'Products' SKU - Multi-Select

 

SCENARIO 1: User selects 2018-12-05 from 'Calendar' Date slicer, with no other filters.

  • AVG % MARGIN formula should perform the following calculation: (35 + 65.3 + 71.4 + 46.4 + 55.6 + 74.9 + 46.2 + 50)/8
  • Since there is no SKU or Category filter, formula includes all 8 SKUs. For each SKU:
    • avg formula selects RegMargin% if product IS NOT on sale on 2018-12-05
    • avg formula selects RegMargin% if product IS on sale on 2018-12-05, but SalePrice is $0.00
    • avg formula selects SaleMargin% if product IS on sale on 2018-12-05, and SalePrice > $0.00
  • AVG % MARGIN visual should show the result i.e. 55.6%

 SCENARIO 2: User selects 2018-12-05 from 'Calendar' Date slicer, and Food,Hardware from 'Products' Category slicer.

  • AVG % MARGIN formula should perform the following calculation: (74.9 + 46.2 + 50)/3
  • Average is computed only for products in the Food and Hardware categories.
  • AVG % MARGIN visual should show the result i.e. 57.03%

SCENARIO 3: User selects 2018-12-05 from 'Calendar' Date slicer, and 11004,11007 from 'Products' SKU slicer.

  • AVG % MARGIN formula should perform the following calculation: (46.4 + 46.2)/2
    • formula selects RegMargin% for 11004, since it IS NOT on sale on 2018-12-05
    • formula selects RegMargin% for 11007, since it IS on sale on 2018-12-05 BUT SalePrice is $0.00
  • AVG % MARGIN visual should show the result i.e. 46.3%

 

 ---------------------------------------------------

 

BILASolution, your solution comes very close, but the Category/SKU filters have to be multi-selectable because we need to be able to calculate average margin for various combinations of SKUs and Categories.

 

Hope this clears things up, thanks a lot guys!

 

 

Ganesh

Hi,

 

Your scenario 3 is incorrect.  SKU 11007 is on sale 2018-12-05.  What should the answer be?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, I have adjusted my response accordingly, please see above.

 

So the formula selects RegMargin% for SKU 11007 because even though the SKU IS on sale on 2018-12-05, the SalePrice is $0.00 so there is no sale.

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Here you go Ashish, virus-free and temporarily hosted on temp.website: https://947b9ee726bf018c.temp.website

 

 

Ganesh

Hi,

 

Has your question been answered by BILASolution or do you need my help?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi everyone!

 

So I have a bunch of products in various categories... and each product has a product cost, regular price, sale price, sale start date, and sale end date...

 

Prods       Categories      ProdCost      RegPrice     SalePrice     SaleStartDate     SaleEndDate    RegMargin%     SaleMargin%

Prod 1     Category A

Prod 2     Category A

Prod 3     Category A

Prod 4     Category B

Prod 5     Category B

Prod 6     Category C

 

RegMargin% = 1-(divide(ProdCost,RegPrice))

This margin stays the same irrespective of date since regular prices don't fluctuate.

 

SaleMargin% = 1-(divide(ProdCost,SalePrice))

This margin would be lower than RegMargin%, but would only apply on dates when a product is on sale.

 

THE PROBLEM:

I need some sort of formula that computes the correct "daily" AVERAGE MARGIN % of multiple products/categories (based on slicers/filters) for each date on my calendar table... For each calendar date, the formula should check if each product is on sale on that date (SaleStartDate < calendar date < SaleEndDate) ... IF prod IS on sale, it'll use the SaleMargin% of that product as part of its average calculation. If prod IS NOT on sale, it'll use the RegMargin% as part of its average calculation.

 

Is there any way to create this type of "dynamic average" that can be used with slicers/filters?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.