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
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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