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

[ganesh575]

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

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[BILASolution]

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

[ganesh575]

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?

[BILASolution]

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

[ganesh575]

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.

[BILASolution]

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

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

[ganesh575]

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.

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

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

[BILASolution · Accepted solution]

@ganesh575

Try this...

https://1drv.ms/f/s!AuU-Ye8UGM4Rko0dyKxTaxuA6pAP8A

BILASolution

[Ashish_Mathur]

Hi,

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

[ganesh575]

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.

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[ganesh575]

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

Ganesh

[Ashish_Mathur]

Hi,

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

[ganesh575]

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?