Forum Discussion
Anonymous
7 years agoNot applicable
Same result when using switch formula
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) Hi Anonymous,
Can you share the file or a dummy one, please? You can mask the sensitive parts first.
Did you make any selections? And only one selection of the two columns a time?
Best Regards,
Dale
2 Replies
- v-jiascu-msftMicrosoft Employee
Hi Anonymous,
Can you share the file or a dummy one, please? You can mask the sensitive parts first.
Did you make any selections? And only one selection of the two columns a time?
Best Regards,
Dale- AnonymousNot applicable
Morning dale. i haved managed to solve the answer