Forum Discussion

Arnault_'s avatar
Arnault_
Resolver III
5 years ago
Solved

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

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

 

 

Need Help
  • Arnault_'s avatar
    Arnault_
    5 years ago

    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's avatar
    v-xulin-mstf
    5 years ago

    Hi Arnault_,

     

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

     

    Best Regards,

    Link

9 Replies

  • Arnault_'s avatar
    Arnault_
    Resolver III
    5 years ago

    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's avatar
      lbendlin
      Super User
      5 years ago

      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_'s avatar
        Arnault_
        Resolver III
        5 years ago

         

        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's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    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_'s avatar
      Arnault_
      Resolver III
      5 years ago

      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.