Forum Discussion

depple's avatar
depple
Helper III
7 years ago

Average based on total population

Hi,   I am trying to do something that is way out of my league, yet should I accomplish it, the output would be really powerful. It might be a trivial solution, but I might not be able to describe ...
Need Help
yelsherif's avatar
yelsherif
Resolver IV
7 years ago

First, for the average part:

use the CALCULATE function and ALL or ALLSELECTED as follows:

AvgSales = DIVIDE(CALCULATE([Sales],ALLSELECTED(Data));'Data[Distribution]))

Now for placing the average instead of zeros, you can use a measure in the table instead of the Sales column:

_Sales = if(SELECTEDVALUE(Data[Sales]) = 0, [AvgSales], SELECTEDVALUE(Data[Sales]))

 

It would be better if you share the pbix file so that I make sure the calculations are correct

  • depple's avatar
    depple
    Helper III
    7 years ago

    Hi yelsherif ,

     

    Thank you for your suggestion.

    I was just about to delete my post, as I managed - by extensive trial & failure - to come up with a working solution. Unfortunately I immediately struck a new problem, because my matrix's vertical sums is returning values that appear to be - if not - close to averages. I have never experienced that before, so I guess that I could have something to do with the extremely homemade measures. I will try your measures, surely they are better, and maybe that'll do the trick!