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 function (I believe because I have added their individual descriptions to the table). The aggregations were giving me a lot of trouble and it took a while to develop these though I'm sure there is a better way:

Group Avg Life = CALCULATE(FORMAT(AVERAGEX('Main Database','Main Database'[Years in Service]),"#,##0;($#,##0)"),FILTER(ALL('Book Table'),'Book Table'[ASSET_GROUP]= MAX(('Book Table'[ASSET_GROUP]))))
 
Group Stdev = CALCULATE(STDEV.S('Main Database'[Years in Service]),FILTER(ALL('Book Table'),'Book Table'[ASSET_GROUP]= MAX( ('Book Table'[ASSET_GROUP]))))
 
The measure I am trying to create is a new one that adds and subtracts Group Avg Life +/- the Group Stdev. My ultimate goal is to concatenate them into a clean range that looks like "6 - 12"
but until I figure out how to at least subtract these two I cannot continue. The measure I created to subtract was as follows:

Group Lbound = [Group Avg Life] - [Group Stdev]

This, however, does not display on my table and I believe it has to do with the aggregations but I am stumped as to how to solve this. I even tried a measure like this:
 
CALCULATE(MAX('Key Measures', 'Key Measures'[Group Avg life]) - MAX('Key Measures','Key Measures'[Group Stdev]))
 
which also didn't work. If anyone can provide some help or possibly a hint towards solving this I would be greatly appreciative!! I've also provided a sample of what the data looks like.
 
GroupItemAverage Group lifeStandard Deviation
Group 1Item 193
Group 1Item 593
Group 1Item 793
Group 1Item 893
Group 2Item 3154
Group 2Item 6154
Group 2Item 10154
Group 3Item 2112
Group 3Item 4112
Group 3Item 9112

 

Thank you for your time,

JE

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.

     

4 Replies

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

         

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

    Hi Anonymous ,

     

    Is this problem solved?

     

     

    Best Regards,

    Icey