Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!!!
Solved! Go to Solution.
Hi,
Share a dataset and show the expected result.
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...
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.
--------------------------------------------------
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:
SCENARIO 1: User selects 2018-12-05 from 'Calendar' Date slicer, with no other filters.
SCENARIO 2: User selects 2018-12-05 from 'Calendar' Date slicer, and Food,Hardware from 'Products' Category slicer.
SCENARIO 3: User selects 2018-12-05 from 'Calendar' Date slicer, and 11004,11007 from 'Products' SKU slicer.
---------------------------------------------------
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?
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.
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?
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |