Average based on total population

[depple]

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 good enough what I am trying to do, but I will give it a shot.

In the visual table below each 0 indicates a Store with no sales of a Product Category. It is filtered, so the hundreds of other Store with sales in both Alfa and Bravo are not displayed.

	Product Category
Store	Alfa	Bravo
Store 1		0
Store 2	0	0
Store 3	0
Store 4		0
Store 5	0
Store 6
Store 7		0
Store 8		0

I would like to make new visual table with the same dimensions and same output of Store, but where there are "0" in visual table above, I would like to display average sales (think sales lost. This average is calculated from all Store with sales, and I have a measure for this. But it is not working in this context, because when I involve Store in table, the average is calculated per Store.

1) How do I alter measure below to disregard the Store dimension of my data? There are other dimensions that I would like to keep, but not Store.

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

2) How do I get my AvgSales in the visual table where the 0's are? Here is the measure for 0 sales:

ZeroSales = VAR StoresWithZeroProduct =

FILTER (

VALUES ( 'Data'[Store] );

NOT ISEMPTY ( FILTER ('Data';[Sales]=0 ))

)

RETURN

CALCULATE( [Sales]; KEEPFILTERS ( StoresWithZeroProduct))

I will really appreciate any input on this. If everything turns out the way I want, the visual table would look like this:

	Product Category
Store	Alfa	Bravo
Store 1		12
Store 2	9
Store 3	9
Store 4
Store 5	9
Store 6
Store 7		12
Store 8		12

 /depple

[yelsherif]

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]

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!