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...
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

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?