AverageX and matrix subtotals

[Dellis81]

Good Morning!

I am having challenges with per unit calculations cascading in an income statement.   I have three related tables

1) IS Header (income statement format)  Dimension Table, 1->many to the Consolidated Transactions table (only detail report lines, excludes report subtotals)

2)  ProjectGroupSetup (includes the Production year)  Dimension Table 1->many to the Consolidated Transactions table

3)  Consolidated Transactions (financial and statistical records)  fact table with relationship to both IS Header and ProjectGroupSetup

In the screenshot below - I have two production (crop) years selected and you can see - for those rows with subtotals, I am lacking the crop acre calculation.    The uniqueness, is these rows have no direct relationship between the fact and IS Header dimension table.

Measure I am using is presented below - and as expected, if I was to remove the All(IS Header) then acres are blanked out.

I have also attempted 

VAR AvgXReportLine =
AVERAGEX (
FILTER (
VALUES ( ProjectGroupSetup[ProdYear] ),
'ProjectGroupSetup'[ProdYear] < YEAR ( TODAY () )
),
VAR calctable =
CALCULATETABLE (
'Consolidated Transactions',
'Consolidated Transactions'[SubHeader] = "CropAcres",
ALL ( ISHeader)
)
RETURN
CALCULATE ( [Sum of Quantity], calctable ))

If I was to just use the Calculate ( [Sum of Quantity], calctable )) - avoid going thru the iterator, then my problems go away.  IT is something relating to the filter and values(years) within the iterator.

Unfortunately those I am working with - want to see average (dollar) values for the selected years - thus I need to use the equivalent averageX denominator.   Summing across multiple years won't work.

Secondly - as I was putting this together - Under Fixed Costs (cash) $1190 - I see Acres has the total acres for both years (4,011 vs the average 2,055.   The difference - 2018 was the only year w/an expenditure.

Always appreciate wisdom and support from this forum!

[kentyler]

So, if there is no crop information for a selected year... what would you like the average to be ? 0 ?

[Dellis81 · Accepted solution]

Thank you for your response - and apologize for delay. Yes, I realize very difficult to grasp without seeing the entire model. But - just yesterday I figured out my problem on missing data - relationship had gotten changed... I don't know why or how - but that seem to resolve the issue. As for matrix subtotals - I am getting closer, but still not satisfied. But - let me repost and try again and bettter clarify the issue.

Also a few days ago, I learned how to get massive data into the "add table" function, and that will greatly help in the future of providing meaningful questions to the forum.

Again thank you - and without help from the forum - novices like me would be lost.

[Dellis81]

Sorry - maybe I misunderstood your question.   If there is no value for a certain year - then treat that year as zero, but include the zero in the average.  thanks!

[kentyler]

People usually write a measure that uses ISBLANK or ISINSCOPE to check some value and then return 0 or the result of a calculation when they want to do something like this. To help with the specific DAX it would help if you could upload a small sample file, either a power bi file or and excel file with some sample data.

[Dellis81]

Thanks for responding!

The quantity of acres should be the same value - all the way down the matrix.

Even for the one year with no dollars posted to the cash fixed costs,

I just noticed, I am having the same problem with the dollars column also.    This style of AverageX works with the calnedar table as part of the filter, but does not work when we exchange with the production year table.
Thanks a million!!!