Reply
Member
Posts: 138
Registered: ‎01-23-2017
Accepted Solution

simple_AVG_formula

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

error to resolve.png


Accepted Solutions
Community Support Team
Posts: 3,819
Registered: ‎07-09-2016

Re: simple_AVG_formula

@nmck86,

 

Simply use ALLSELECTED instead of VALUES.

Community Support Team _ Sam Zha
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


All Replies
Member
Posts: 138
Registered: ‎01-23-2017

Re: simple_AVG_formula

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

Super User
Posts: 3,755
Registered: ‎01-14-2017

Re: simple_AVG_formula

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.

Highlighted
Member
Posts: 138
Registered: ‎01-23-2017

Re: simple_AVG_formula

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!

Link to file

 

HELP IMAGE.png

Member
Posts: 138
Registered: ‎01-23-2017

Re: simple_AVG_formula

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

Super User
Posts: 3,755
Registered: ‎01-14-2017

Re: simple_AVG_formula

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

Member
Posts: 138
Registered: ‎01-23-2017

Re: simple_AVG_formula

@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! 

 

Screen Shot 2018-12-10 at 8.49.15 PM.png

Member
Posts: 138
Registered: ‎01-23-2017

Re: simple_AVG_formula

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

Community Support Team
Posts: 3,819
Registered: ‎07-09-2016

Re: simple_AVG_formula

@nmck86,

 

Simply use ALLSELECTED instead of VALUES.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.