cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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?

1 ACCEPTED SOLUTION
Solution Specialist
14 REPLIES 14
Super User

Hi,

Share a dataset and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Specialist

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

Frequent Visitor

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?

Solution Specialist

Sure, use this measure...

```% Margin 2 =

var d = SELECTEDVALUE('Calendar'[Date])
var prod = SELECTEDVALUE('Product'[Prods])
var startdate = LOOKUPVALUE('Product'[SaleStartDate];'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

Frequent Visitor

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.

Solution Specialist

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

Frequent Visitor

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

Solution Specialist
Super User

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
Frequent Visitor

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.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Ganesh

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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?

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors