Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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]))
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |