cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

 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

JE

1 ACCEPTED SOLUTION
Community Support

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 4
Community Support

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

Community Support

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

Best Regards,

Icey

Anonymous
Not applicable

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

Community Support

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors