A single value in column [CMI] in file....cannot be determined...

[ThomasDay]

Hello all--yes, this is a very common error.  Part of the DAX learning experience I guess.  There are many variations it seems.

 The measures (derived from measures) I'm looking to create are of the form like this: ALOS_CMI Adj = DIVIDE( [ALOS], AdjustmentFactors[CMI]). 

• The AdjustmentFactors table is related 1-many to the table [ALOS] is created in/from.  
• For any single Provider's measure, there is ONE AdjustmentFactor[CMI].
• I'd sure like totals to total up the resulting calc, not the total [ALOS] divided by total [CMI]

If I use DIVIDE ( [ALOS], Min(AdjustmentFactors[CMI])) that makes it a scalar...and the individual results are good.  Doesn't that make every average wrong? (though I don't plan to compute averages for a group)

So what are the options? Do I really have to store the resulting calc somehow?

Thanks,

Tom

[Greg_Deckler · Accepted solution]

Try using a VAR, see the blog article here:

http://community.powerbi.com/t5/Community-Blog/DAX-as-a-Language/ba-p/22120

[ThomasDay]

Thanks very much.  I'll try it tomorrow!  Tom

[ThomasDay]

@Greg_Deckler That worked great.  Here's the pattern I ended up using.

XXX Adj =
// Each facility is a single scalar, but the totals are an average of the CMI's--and not adjusted
// for the case counts in each facility
VAR CMIVAL = DIVIDE(SUMX(FR2015_standardizing_file, [CMI]),COUNTX(FR2015_standardizing_file,[CMI]))
RETURN
(DIVIDE([XXX],CMIVAL))

Thanks,

Tom