Hi,
I have a measure that is working to find the total cost of product service. It is being ranked and shown for top 3 products and remaining products are grouped together as Other. My data set however, is over 2M rows and I have other similar measures that find out the breakdown of the cost (material component, labor, travel, admin costs etc.). So, I am ending up repeating the same calculation of ranking for each of this measure. The ranking is again repeated for finding the associated costs from Previous Year (or Quarter) to find VPY and VPQ (Variance with regard to previous year or previous quarter). Hence, a couple dozen measures are now in the model doing this ranking. Is there a way to somehow do this ranking once and then each measure can reference and use it?
Note that in the pivot reports, the product list is sliced by categories and sub categories so total rows in the pivot are not 4 (but more like 20-30).
Here are my measures for which I'm looking for your advice:
1. This is a ranking measure that ranks products by their spend. If a product cost is less than 5% of the category, then that is excluded from ranking. So, in some cases I only see top 1 product and rest are grouped as Others.
RankX_rnk:=VAR Rank_threshold = 0.05 // 5% of total cost
VAR TC = CALCULATE([Total_Cost], ALL(tbl_Prd[Prod_name]))
return
IF([Total_Cost] < TC * Rank_threshold, 5, // Do the ranking only for values bigger than 10% of modality cost
RANKX(
ALL(tbl_Prd[Prod_name]),
[Total_Cost],
// Leave Value paramter blank
,DESC,Skip ) )
2. I have an unconnected Ranking table in the model where Rank 1 has a min of 0 and max of 1. Others is row 4 in that table with a min of 3 and Max of 1000. So, anything assigned a 5 (from the above measure) is moved to Others group per the following top cost measure:
Top_Costs:=CALCULATE(
[Total_Cost], // simple measure using a sum formula to add costs
FILTER(
VALUES(tbl_Prd[Prod_name]),
COUNTROWS(
FILTER(
Ranking,
[RankX_rnk] > Ranking[Min]
&&
[RankX_rnk] <= Ranking[Max]
)
) > 0
)
)
3. Then I have a previous period calculation, that also checks the user slicer selection for PY or PQ. Performance killer however, is the fact that ranking is done again for each of these measures:
Cost_Prev:=VAR PQ_comp = PARALLELPERIOD('Calendar'[Date_Long], [MNum]*-1, MONTH)
VAR PY_Comp = SAMEPERIODLASTYEAR('Calendar'[Date_Long])
return
IF(
HASONEVALUE(Comp_Sel[Prv_Year or Qtr]), // A value is picked by user
// True case for If statement
IF(CONTAINS( Comp_Sel, Comp_Sel[Prv_Year or Qtr], "PQ"), // Comparison with previous quarter needed
CALCULATE(
[Total_Cost],
FILTER(
VALUES(tbl_Prd[Prod_name]),
COUNTROWS(
FILTER(
Ranking,
[RankX_rnk] > Ranking[Min]
&&
[RankX_rnk] <= Ranking[Max]
)
) > 0
),
PQ_comp),
// in the false loop, comparison against Previous year is needed
CALCULATE(
[Total_Cost],
FILTER(
VALUES(tbl_OPH[OPH Family]),
COUNTROWS(
FILTER(
Ranking,
[RankX_rnk] > Ranking[Min]
&&
[RankX_rnk] <= Ranking[Max]
)
) > 0
),
PY_Comp)
), // Following is the case when no selection is made
"PY or PQ?" // One value was not picked - false condition
)
