- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 10:34 AM

Hello,

I am running into an issue trying to get a simple avg formula to work inside Power BI for this particular case. In the image below you will notice that I have a category breakdown and then to the right I have the total score and total score average. The total score and the average are correct; however, the average I am getting for the category breakdown is wrong. I am expecting the HAPPY CUSTOMERS - MAD CUSTOMERS TO EQUAL 52.28% and it is way off from that. I am sharing the link to the pbix if anyone can assist.

https://drive.google.com/open?id=1siXptcM3FGuvQ3VH4qbC3aBdgFUa0Dgl

Solved! Go to Solution.

Accepted Solutions

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-13-2018 01:55 AM

Simply use ALLSELECTED instead of VALUES.

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*All Replies

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 02:11 PM

@OwenAuger @Nick_M @Ashish_Mathur this has a link to it so you can see the data as it is currently. Let me know if you can assist with it through the pbix file shared.

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 03:37 PM

Hi,

Try this measure

=Category_Breakdown = if(HASONEVALUE('download to use'[Category]),DIVIDE([WT],CALCULATE([WT],ALL('download to use'[Category]))),[avg])

Hope this helps.

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-09-2018 12:13 PM

HI @Ashish_Mathur @Nick_M and @OwenAuger @v-jiascu-msft unfortunately that does not work. In this situation I am getting the same result as I had originally.

Let me explain. Category_Breakdown ties to Total_Score and that is correct. However, the Category_Breakdown2 (the formula you shared) is giving me the same results as Category_Breakdown. When I do the math of **happy-mad** I expect **Category_Breakdown** to tie to **Total_Score** and it does! However, I need this to be the same for the new AVG formula that I am leveraging. I need my **AVG formula** to tie back to the **Category Breakdown2**; however, it doesn't. Essentially, I need my **happy-mad=avg**.

Let me know something happens to not make sense!

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 05:34 PM

@Ashish_Mathur Hi Ashish did you have any thoughts on how I could make this work? I am open to alternatives if the AVERAGE(SUMMARIZE dax may not would work.

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 05:36 PM

Your question is not clear to me. That is why i did not contribute.

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 05:53 PM

@Ashish_Mathur @v-jiascu-msft I am hoping to get my average category breakdown amounts happy customers 71.35% and mad customers 27.24% to equal my average 52.28%. Basically the average function is disregarding months with no values. I want my calculation to do a straight average over the months selected. For instance, Jan-October I want an average over all 10 months even if a few months do not have values. See below the mad customers does not have a value for every month so it is bringing in a percentage of 27.24 which is the avg of the months with values and it is excluding any month that doesn't currently have a value. Hope this makes more sense for sure!

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 05:57 PM

@Ashish_Mathur this seems very close to what you did here in this post https://community.powerbi.com/t5/Desktop/including-blanks-in-an-average-with-summarize/td-p/332922

I tried to use that function but it didn't work for me.

## Re: simple_AVG_formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-13-2018 01:55 AM

Simply use ALLSELECTED instead of VALUES.

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*