Calculate against a distribution curve

[fraitaan]

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?

[Anonymous]

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

[fraitaan]

Hi, 

Thanks for answer. 

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