MdxScript(Model) / Calulcation error in (...)

[Arnault_]

Hi All,

I have created several mesures to define a confidence interval for my data.

The measures seem to work fine when I use the "card" visual. When I try to use a "table" visual, I get that error message. Here is the detailed message below. You will also find a link with the pbix.

Any idea how to fix this?

Thanks in advance

[Arnault_ · Accepted solution]

Finally, I have tried the following and it works (it seems).

Selling price - conf. level 95% = 
VAR Alpha = ( 1 - 0.95 )
VAR Size =
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'FACT Order'[packing_line_id] ) ),
        FILTER ( 'FACT Order', 'FACT Order'[selling price] <> 0 )
    )
VAR SDev = [Selling price (Std Dev)]
VAR Result =
    CONFIDENCE.NORM ( Alpha, SDev, Size )
RETURN
    IF ( SDev > 0,  Result, BLANK () )

[Arnault_]

Hi community,

I am getting en error with one of the measure I created. My objective was to build a confidence interval.

The measures works when displayed in a "card" visual and does not when displayed in a "table".

Any idea how to fix this?

Thanks 😉

Here is the pbix.

Here is the measure's code:

Selling price - conf. level 95% = 
VAR Alpha = ( 1 - 0.95 )
VAR Size =
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'FACT Order'[packing_line_id] ) ),
        FILTER ( 'FACT Order', 'FACT Order'[selling price] <> 0 )
    )
VAR SDev = [Selling price (Std Dev)]
VAR Result =
    CONFIDENCE.NORM ( Alpha, SDev, Size )
RETURN
    IFERROR ( ROUND ( Result, 3 ), BLANK () )

Here is a screenshot with the error message below:

[lbendlin]

Try returning 

[Selling price (Std Dev)]

instead. Most likely that measure is not designed properly and creates a value that is too big for the computation (probably summing up too much)

[Arnault_]

Hi @lbendlin ,

Is it an assumption? Can you be more specific if you have any idea?

Anyway, I have found out that the issue seems to be related to that measure, however, it looks like it does not work when the standard deviation result is blank. I have tried to handle this blank result without success. This is where I am expecting some support from the coommunity. How can I make sure the result of [Selling price - conf. level 95%]  is blank and not an error when [Selling price (Std Dev)] cannot be calculated? 

[v-xulin-mstf]

Hi @Arnault_,

The confidence interval can not  correspond to a single line.

And then you need modify the data type of mrasure [Selling price - conf. level 95%].

Here is the output:

Best Regards,

Link

[Arnault_]

Hi @v-xulin-mstf ,

Well, what type should I use? I don't get it.

BTW, I figured out that the measure does not work when the standard deviation cannot be calculated.

Indeed [Selling price - conf. level 95%] depends on [Selling price - Std Dev]. I have tried to handle this error but without succeeding. How can I proceed? The expected output would be: if Std Dev is blank or produces an error, then conf. level = blank.

[v-xulin-mstf]

Hi @Arnault_,

If [Selling price - Std Dev] returns the value that does not match the condition, CONFIDENCE.NORM returns the #NUM! error value.

So it is impossible that the measure return blank.

Please refer: https://docs.microsoft.com/en-us/dax/confidence-norm-function-dax

Best Regards,

Link

[Arnault_]

Ok, that's the problem. I thaught that we could handle this issue. If one of the argument is not matching, why isn't it possible to get an alternative output which in that case could be "blank" as I suggested.

Something like:

If [Selling price (Std Dev)] ≤ 0, then blank(), otherwise [Selling price - conf. level 95%].

[Arnault_ · Accepted solution]

Finally, I have tried the following and it works (it seems).

Selling price - conf. level 95% = 
VAR Alpha = ( 1 - 0.95 )
VAR Size =
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'FACT Order'[packing_line_id] ) ),
        FILTER ( 'FACT Order', 'FACT Order'[selling price] <> 0 )
    )
VAR SDev = [Selling price (Std Dev)]
VAR Result =
    CONFIDENCE.NORM ( Alpha, SDev, Size )
RETURN
    IF ( SDev > 0,  Result, BLANK () )

[v-xulin-mstf · Accepted solution]

Hi @Arnault_,

Congratulations, my idea is also like: IF ( SDev > 0, Result, BLANK () )

Best Regards,

Link