Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Simply use ALLSELECTED instead of VALUES.
@OwenAuger @Anonymous @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.
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.
HI @Ashish_Mathur @Anonymous 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!
@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.
Your question is not clear to me. That is why i did not contribute.
@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.
Simply use ALLSELECTED instead of VALUES.
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |