Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Adding and subtracting Average and standard deviation

Hello,   I am working on a dashboard that calculates the group average life of an asset and the group standard deviation of that life. I created two measures for this that had to use the MAX functi...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    I have no access to your dummy .pbix file. So I create one based on the dummy sample data you provided. And the [Group Lbound] measure reutrns value.

    I find that the data type of [Group Avg Life] is "Text". Maybe this is the cause. Try to create your [Group Lbound] measure like so:

    Group Lbound = CONVERT ( [Group Avg Life], DOUBLE ) - [Group Stdev]

     

     

    Best Regards,

    Icey

     

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

     

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

Please share me some sample data of both "Main Database" and "Book Table" or just a dummy .pbix file. Please ensure that the structure of the sample data is consistent with the real data. Please remove sensitive information and replace real data with dummy data. 

It is suggested to upload your file to OneDrive for Business and then paste the link here.

 

 

Best Regards,

Icey

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Icey,

     

    Here is the link to the excel dummy file I made - https://1drv.ms/x/s!AqlgOE-KAGt_zkvY2U9A7lAQt9HH?e=r2yVbK

    Here is the link to the dummy pbix file I made - https://1drv.ms/u/s!AqlgOE-KAGt_zk3Id5OzD7wkO5B1

     

    Sorry about taking so long to respond, it took me a bit of time to actually create these. The maddening part about this is that in the dummy file the two measures subtract! Everything is the same logic and exact code but it works in one and not the other. Obviously my dataset is much larger than this so I am trying to figure out what is going wrong. My thoughts are there is something in the dataset itself that is causing the display to break. So far the only ways I've been able to make the "Difference" measure to not display in the dummy is by having a years in service that is blank or negative. None of which exist in my database. If you have any other ideas on what may cause this measure to not work it would be greatly appreciated to be able to solve this.

    Thanks again!

    -JE

    • Icey's avatar
      Icey
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      I have no access to your dummy .pbix file. So I create one based on the dummy sample data you provided. And the [Group Lbound] measure reutrns value.

      I find that the data type of [Group Avg Life] is "Text". Maybe this is the cause. Try to create your [Group Lbound] measure like so:

      Group Lbound = CONVERT ( [Group Avg Life], DOUBLE ) - [Group Stdev]

       

       

      Best Regards,

      Icey

       

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