Average in a matrix with hours and week days, results are too low

[Anonymous]

I'm trying to create a heatmap, with weekdays (TableB) in the columns, and hours (TableC) in the rows and an average value (TableA)

Table A where the value is located looks something like this:

Date	Hour	ItemID	PersonID	ItemID_2	UnitsSold
2024-04-03	13	AC1	Bob	123	100
2024-04-03	13	AC2	Bob	123	50
2024-04-03	13	AC1	Sam	321	100
2024-04-03	14	AC1	Bob	321	200

There could be tens of rows per hour per PersonID (per day)

ItemID_2 has a relation to a lookup table that we use to filter out the correct number of units sold. 

My issue here is that a simple calcualtion of 

var _unitssold = CALCULATE(

    AVERAGE('TableA'[UnitsSold]),

        d_lookup[Metrics | Lookup]   = "Count",

        d_lookup[Exclusion | Lookup] = 0

    )

RETURN

_unitssold

This gives me waaaaaaay to low numbers. And I'm not entirely sure why.

I've tried using removefilters for weekday and hour but that's not it either.

Something like this but with averages

Hour	Mon	Tue	Wed	Thu	Fri	Sat	Sun
1
2
3
4
5
6
7
8

[Anonymous · Accepted solution]

Solved: 

I had previously tried using sum instead of average, and then dividing with the number of days in the selected period. 

I double checked and it seemed that the variable counting days was way off. after I fixed that the numbers now makes sense... 

[foodd]

Hello @Anonymous , and thank you for sharing a question with the Community.   This reply is informational.  Please  remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!