cancel
Showing results 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

Frequent Visitor

Calculate against a distribution curve

Hi,

Won't be able to share data.

I work with warranty. Our product (3 different) already have a distribution curve of how many % of the products that could possibly break down during the warranty period.

Period = Months
Attribute = Product(s)
Value = How many % is expected to be a warranty case

Picture 1: Showing part of the distribution curve

For every product made, we take away a small portion that will cover our warranty costs.
So it's produced volume that month * small portion of the sales price = Init.Prov to cover our warranty costs

From the Init.Prov amount we calculate the expected cost. So it would be Init.Prov * Distribution Curve = Expected costs for that period

Thereafter we take Actual cost minus Expected cost to get the Delta (real cost)

Here is where the problems start, we are able to get the correct data in a waterfall chart but not able to detail it in a matrix/table.
Problem seems to be that the subtotal and total of that year, the matrix want to Avg the % value but it should just sum the expected cost and not take Distribution curve * Init.Prov in the total. Right now the subtotal is the defining value, not each period/months. In the end calculation, the expected cost is about 75% to high than what it should be.

The formula that is used to calculate the expected cost, it works in the waterfall chart but not in a ordinary matrix:

Expected = LASTNONBLANK('DistributionCurve[Value], TRUE()) * [Provision]

Question:

How can I stop/make a new DAX for the Matrix from averaging the DC-value for the sub-total/total?

2 REPLIES 2
Community Support

Hi  @fraitaan ,

I created some data:

It's not very clear what you mean..... Are you referring to encountering calculations in the matrix that you want to be different at different levels?

You can use IF+ HASONEVALUE() to determine which level you are in and perform the corresponding calculation.

HASONEVALUE function (DAX) - DAX | Microsoft Learn

IF function (DAX) - DAX | Microsoft Learn

Here are the steps you can follow：

1. Create measure.

Measure =
IF(
HASONEVALUE('Table'[Month]),1,
IF(
HASONEVALUE('Table'[Year]),2,3))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Frequent Visitor

Hi,

Not sure if it will solve my problem.
At least I don't understand how to use it in my formula.

Period 1 (month) = Warranty cost should be 2% of init.prov
Period 2 (month) = Warranty cost should be 3% of init.prov
Period 3 (month) = Warranty cost should be 5% of init.prov

etc.

The problem is that my matrix (after 1 whole year) is doing a avg. of the %-value and multiply it with the provision and use it in all my other calculations.

 Month (Period) Init.Prov Distribution Curve (DC) (%) Expected Jan (1) 10000 25 (%) 2500 Feb (2) 10000 23 (%) 2300 Mars 10000 21 2100 April 10000 18 1800 May 10000 16 1600 Jun 10000 13 1300 Jul 10000 11 1100 Aug 10000 8 800 Sep 10000 5 500 Oct 10000 3 300 Nov 10000 2 200 Dec 10000 1 100 Wrong Total (that my matrix do) 120000 12,16 (%) 14592 Correct Total (That I want) 120000 (Blank) 14600

I hope that I made it more understandable.
Should've made this illustration in the first post :)!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.