topic Re: Average by a group in DAX Commands and Tips

Average by a group

Anonymous — Fri, 21 May 2021 10:57:00 GMT

Hi,

I've tried more than 20 other solutions on similar problemt - none have worked so far.

I want to create a measure calculating the average category value share for each chain of stores (as seen below). this will then allow me to calculate any potentiale an underperforming store possesses.

The category value share is already calculated (measure).

Currently the wholesaler, chain and stores are stored in separate columns of the same table.

Example below:

	category value share	category value share (chain)	Potentiale
Chain	10%	10%	-
- Store 1	5%	10%	5%
- Store 2	15%	10%	-

Following tables are used:

Table1 = Store data

Table2 = Category sales data

I have tried - among many others - the following:
= calculate(Table1[category value share],table1[chain])

Sincerely
Andreas D.

Re: Average by a group

SivaMani — Fri, 21 May 2021 11:19:41 GMT

Anonymous, Did you try the below measure?

calculate(AVERAGE(Table1[category value share]),ALL(table1[store]))

Re: Average by a group

Anonymous — Fri, 21 May 2021 11:22:59 GMT

Hi Siva,

When using the average function it does not recognize the category value share measure.

Re: Average by a group

SivaMani — Fri, 21 May 2021 11:29:07 GMT

Anonymous, Is it already a measure? If yes, the AVERAGE function doesn't require

calculate([category value share],ALL(table1[store]))

Re: Average by a group

Anonymous — Fri, 21 May 2021 11:44:11 GMT

Hi again,

The table shown in the example is the end-product which i seek to asctertain.

Currently i have calculated the category sales value as follows:
= (Divide(sales,totalsales))*(divide(totalsales/category value))

It is calculated per store and sums as intended to the given hierarchy of chains and wholesalers. I simply need to compare the individual store average to the average of the chain of stores to which it belongs. Example: a store may have a category share of 10%, whilst the average of all the stores within that chain is 15%. This one store is then underperforming and has an initial growth potential of 5%. 🙂

Re: Average by a group

Anonymous — Fri, 21 May 2021 11:50:17 GMT

Upon using your measure i get the following:

I need every store to take the average of the chain ("Bilka" in this case)

Re: Average by a group

SivaMani — Fri, 21 May 2021 12:30:33 GMT

Anonymous, It should work. I hope Bilka is just another value under the chain column.

If possible, share the pbix possible. will have a look.