Iterating with SUMX and AVERAGEX in the same calculation

[ThomasDay]

Hello All,

I've been working on this for quite some time and have hit a wall.  I need to compute a formula for totals of a matrix where the total I'm looking for is in essence (ScrdPricePerUnit - CompPricePerUnit) * ScrdTotUnits iterated across CPT codes rolled up into Departments..  Simple idea 

The data table that these are computed from looks like this:  Note CompPrice records use the slicer/relationship while ScrdPricePerUnit and ScrdTotUnits use FILTER

Here's an excel table exported from the matrix I'm working with to illustrate what I need:

Computing Comparison Price per Unit from a potentially long list of comparisons is:

AvgCompPricePerUnit =  CALCULATE(AVERAGEX(DataTable,DataTable[ModeChgPerUnit]), ALLSELECTED(DataTable[CPT/HCPCS]))  It iterates across CPT's then to DEPT.  Works great.

Scrd Prices and Units don't use the slicers/relationship...rather I filter down to a single facility and use SUMX. 

ScrdPricePerUnit =  CALCULATE(SUMX(DataTable,DataTable[ModeChgPerUnit]), FILTER(DataTable, DataTable[ProviderID] =  SELECTEDVALUE(ScrdProviders[Provider ID])))

Similarly Scrd Tot Units is the same:

ScrdTotUnits = CALCULATE(SUMX('DataTable','DataTable'[TotalUnits]),FILTER('DataTable', 'DataTable'[ProviderID] = SELECTEDVALUE(ScrdProviders[Provider ID])))

So I don't know how to get the iterations into a single computation since the Scrd items use SUMX and FILTER and the Comparison price uses AVERAGEX and ALLSELECTED.

Any ideas?  

I'm hopeful that one of you geniuses has ideas...maybe a calculate table or other strategy.

Thank you,

Tom

[TeigeGao]

Hi @ThomasDay ,

Could you please share the pbix file to us for analysis?

Best Regards,

Teige

[ThomasDay]

The following calcs worked great.  I'm opening another issue though so I'll post it with a model as suggested by @TeigeGao .

//This first calc takes a multi row per code table and make it one row per code using the magical SUMMARIZE...so it's the same shape as the Scrd Price per Unit.  Thank you @Greg_Deckler for the examples

Comparison Price per Unit = VAR CompAvg = SUMMARIZE('DataTable','DataTable'[CPT/HCPCS],

"ChargesPerUnit",AVERAGE('DataTable'[Price per Unit]))

                        RETURN

                        AVERAGEX(CompAvg,[ChargesPerUnit])

//There is one row per code for the Scrd Price Per Unit

Scrd Price Per Unit =

CALCULATE(AVERAGEX('DataTable','DataTable'[Price per Unit]),

        FILTER('DataTable', 'DataTable'[ProviderID] , SELECTEDVALUE(ScorecardProvider[ProvdrID])))

Tot Scrd Cases = CALCULATE(SUMX('DataTable','DataTable'[TotalUnits]),

                            FILTER('DataTable', 'DataTable'[ProviderID] = SELECTEDVALUE(ScorecardProvider[ProvdrID])))

//Since the summarized ChargesPerUnit for comparisons are one row per code now, the AverageX does the subtraction during the iteration!

Total Delta = VAR CompAvg = SUMMARIZE('DataTable','DataTable'[CPT/HCPCS],

"ChargesPerUnit",AVERAGE('DataTable'[Price per Unit]))

         RETURN

         SUMX(CompAvg,AVERAGEX(CompAvg,(([Scrd Price Per Unit] - [ChargesPerUnit]) ) * [Tot Scrd Cases]))