topic Incorrect Average Calculation in DAX Commands and Tips

Incorrect Average Calculation

Anonymous — Tue, 28 Jul 2020 07:07:50 GMT

Hi everyone,

I am very new to Power BI (and definitely to DAX) and I can't seem to figure out where the error is, hope someone out there can help me out!

What I want to achieve, is getting an average per store, weighted by store visits.

STORES	STORE VISITS
Store 1	3
Store 2	4
Store 3	6
Store 4	2

	STORE 1	STORE 2	STORE 3	STORE 4
PRODUCT A	7	5	7	4
PRODUCT B	3	1	6	8
PRODUCT C	6	4	5	5

So if we would go from this example, I would need to get following table:

	STORE 1	STORE 2	STORE 3	STORE 4
PRODUCT A	2,33	1,25	1,17	2
PRODUCT B	1	0.25	1	4
PRODUCT C	2	1	0.83	2,5

I don't get this result just by calculating average, though. It seems that it only divides the number of products by the amount of store visits which was needed to find it.

So for example: If Product A was found 7 times in Store 1; in total there have been 3 store visits in Store 1; but Product A was found 7 times in only 2 store visits => It will divide 7/2.

Other things u might need to know:

Store Visits is a measure: First I Concatenate Adress & Date -> Separate Store Visit
Then I Distinctcount that calculated column.

I also have another Calculated Column to show the amount of store visits per store.
I tried putting everything in 1 measure

The closest I've gotten so far is:

AVERAGEX(

KEEPFILTERS(VALUES('Data'[SeperateStorevisits])),

CALCULATE(COUNTA('Data'[Product]))

)

I am sure it has to be something really simple that I don't know about yet, but I know you guys can help me out!

Much appreciated!

Cheers,

Joris

Re: Incorrect Average Calculation

lbendlin — Wed, 29 Jul 2020 13:27:19 GMT

Your sample tables are inconsistent with your explanation. The first table shown seems to be an aggregate - we would need to see the raw data. The second table hopefully is just a visual as well. If not then you MUST unpivot it before proceeding.

Re: Incorrect Average Calculation

Anonymous — Wed, 29 Jul 2020 13:42:32 GMT

HI Anonymous ,

Not very clear.

Can you share a sample .pbix or sample data in text format.

Regards,

Harsh Nathani

Re: Incorrect Average Calculation

Anonymous — Fri, 31 Jul 2020 11:36:50 GMT

Hey,

Okay I tried converting my data in sample data, hopefully, this makes it more clear. The data shown is limited, but I assume it should be enough to make my point.

.pbix Sample File

I added the needed tables for information:

- I want to calculate the average of times Product Type appears per Store, divided by the amount of Store Visits.

=>This way I want to have a sort of weighted average to compare how often a Product Type appears in each store.

- The way it calculates the average now: It doesn't divide the total times the Product Type appears, by the total amount of Store Visits, just by the amount of Store Visits whereby this Product Type was registered.

I hope this makes more sense now...

Thanks in advance, internet wizards!

Re: Incorrect Average Calculation

Fowmy — Fri, 31 Jul 2020 18:46:14 GMT

Anonymous

Can you try this measure please:

Measure = DIVIDE( COUNT(DatalijstWinkelbezoek[Product Type]), CALCULATE( [#Storevisits], ALLSELECTED(DatalijstWinkelbezoek[Product Type]) ) )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Re: Incorrect Average Calculation

Anonymous — Mon, 03 Aug 2020 10:43:03 GMT

Thanks a lot!
I feel like I've tried very similar techniques but never got it right, I knew it was going to be something simple..