Adding and subtracting Average and standard deviation

[Anonymous]

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.

Group	Item	Average Group life	Standard Deviation
Group 1	Item 1	9	3
Group 1	Item 5	9	3
Group 1	Item 7	9	3
Group 1	Item 8	9	3
Group 2	Item 3	15	4
Group 2	Item 6	15	4
Group 2	Item 10	15	4
Group 3	Item 2	11	2
Group 3	Item 4	11	2
Group 3	Item 9	11	2

Thank you for your time,

JE

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Icey]

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]

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 · Accepted solution]

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.