topic Incorrect Total in Matrix - Trying to Produce Average in the Total in DAX Commands and Tips

Incorrect Total in Matrix - Trying to Produce Average in the Total

auzzieb — Thu, 04 Jan 2024 15:09:03 GMT

Thank you in advance for the help, so I have the below DAX measure that is technically producing the correct values for each month, however my total is incorrect. I want my total to produce the average of all the months in 2023 below. So the correct average would be $18.65 instead of $26.58 (per below screenshot). How can I change my measure to do this? I believe I need an AVERAGEX or a HASONEVALUE function, but not sure about the proper syntax.

WTI - WCS Diff ($US/bbl) - Hardisty =

VAR LastDayWithPrice =

CALCULATE(

LASTNONBLANKVALUE(Data[PriceDate], MAX(Data[PriceDate])),

Data[symbol]="#DG.MO_INDEX.WCS_HRD/CURVE/M00")

VAR Price =

CALCULATE(

[Average Close],

Data[PriceDate]=LastDayWithPrice,

Data[symbol]="#DG.MO_INDEX.WCS_HRD/CURVE/M00")

RETURN

ABS(Price)

Matrix Visual (total is incorrect, monthly values are correct). I want the total to be an average of the monthly values.

Fields in Matrix:

Re: Incorrect Total in Matrix - Trying to Produce Average in the Total

lbendlin — Fri, 05 Jan 2024 00:36:40 GMT

You don't really need any code. Set the implicit aggregation of the WTI -WCS field to "average" rather than "sum"

Re: Incorrect Total in Matrix - Trying to Produce Average in the Total

auzzieb — Fri, 05 Jan 2024 14:15:27 GMT

But this is a measure not a column. I can't just easily change the aggregation with the measure like I can with a column.

Re: Incorrect Total in Matrix - Trying to Produce Average in the Total

lbendlin — Fri, 05 Jan 2024 15:15:25 GMT

In that case your approach is the way to go. Use HASONEVALUE to figure out where in the matrix you are, and adjust the computation accordingly.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to 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.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523