Hi Experts
I have the following switch formula to pull back the Top 10 Best performing product, but the switch formula keeps on giving me the same result for each product ...... i have check each of the individual measures and they all produce different values (end results)...
can see the error
Top Products Variance Gross Margin / Net Sales =
VAR RankingContext = VALUES('Product'[Category])
VAR ProfitbabilityMeasure = SELECTEDVALUE(ProfitabilityMeasures[MeasureName])
VAR ReportingMeasure = SELECTEDVALUE(Reporting[Reporting])
RETURN
SWITCH(TRUE(),
ProfitbabilityMeasure = "Gross Margin" && ReportingMeasure = "Reported"
, CALCULATE( [GMvPY1% (Reported PY)]
, TOPN(10, ALL('Product'[Category]),[GMvPY1% (Reported PY)])),
ProfitbabilityMeasure = "Gross Margin" && ReportingMeasure = "NOE"
, CALCULATE( [GMvPY4% (NOE @ AOP Rate)]
, TOPN(10, ALL('Product'[Category]),[GMvPY4% (NOE @ AOP Rate)])),
ProfitbabilityMeasure = "Gross Margin"
, CALCULATE( [GMvPY1% (Reported PY)]
, TOPN(10, ALL('Product'[Category]),[GMvPY1% (Reported PY)])),
ProfitbabilityMeasure = "Net Sales" && ReportingMeasure = "Reported"
, CALCULATE( [NSvPY1% (Reported PY)]
, TOPN(10, ALL('Product'[Category]),[NSvPY1% (Reported PY)])),
ProfitbabilityMeasure = "Net Sales" && ReportingMeasure = "NOE"
, CALCULATE( [NSvPY4% (NOE @ AOP Rate)]
, TOPN(10, ALL('Product'[Category]),[NSvPY4% (NOE @ AOP Rate)])),
ProfitbabilityMeasure = "Net Sales"
, CALCULATE( [NSvPY1% (Reported PY)]
, TOPN(10, ALL('Product'[Category]),[NSvPY1% (Reported PY)])),
RankingContext)
