Convert Totals on Matrix to Average

[jbrown184]

Hi I am trying to Convert Totals on Matrix to Averages. The values is a count of specific column (Transactions). See below. How do I do this?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file. 

[jbrown184]

How do I share a download link?

[Ashish_Mathur]

Upload the file to Google Drive and share the download link.

[jbrown184]

Here ya go!

https://drive.google.com/file/d/1PPBJxNTDVODVd03KKgLBvcFlRO_nKkzi/view?usp=drive_link

[Ashish_Mathur]

Access Denied message.

[jbrown184]

Okay you should be able to download it now.

[Ashish_Mathur]

Hi,

Try these measures

Measure = COUNT('Location History'[Transaction])

Measure 2 = if(HASONEFILTER('Location History'[Full Name]),if(HASONEFILTER('Location History'[Production Hour]),[Measure],AVERAGEX(VALUES('Location History'[Production Hour]),[Measure])),AVERAGEX(VALUES('Location History'[Full Name]),[Measure]))

Hope this helps.

[jbrown184]

Thanks! That worked! One more ask. Is there anyway to get the total (The one circled in Red) to be the average of everything from the left?

[Ashish_Mathur · Accepted solution]

Hi,

Measure 2 can be simplifies to:

Measure 2 = AVERAGEX(GENERATE(VALUES('Location History'[Full Name]),VALUES('Location History'[Production Hour])),[Measure])

Hope this helps.

[jbrown184]

Thats amazing thanks!

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi @jbrown184 ,
Based on your description, I think your question is about the handling of hierarchies, you can use the ISFILTERED function to determine if you are affected by the hierarchy in which you are located, and thus use if judgments to differentiate between situations. You can refer to this article for better use of hierarchies:
Clever Hierarchy Handling in DAX - SQLBI
In the meantime you can also refer to this post for a reply
Solved: Changing TOTAL on a Matrix Visual from a SUM to an... - Microsoft Fabric Community
You can refer to this official document for more about ISFILTERED
ISFILTERED function (DAX) - DAX | Microsoft Learn

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly