Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

1 ACCEPTED SOLUTION
Icey
Community Support
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.

 

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Is this problem solved?

 

 

Best Regards,

Icey

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

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

 

 

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

Icey
Community Support
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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.