Incorrect Average Calculation

[Anonymous]

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

[Fowmy · Accepted solution]

@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

[Anonymous]

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..

[harshnathani]

HI @Anonymous ,

Not very clear.

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

Regards,

Harsh Nathani

[Anonymous]

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!

[lbendlin]

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.