Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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-msft's avatar
    v-jiascu-msft
    Microsoft 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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Morning dale. i haved managed to solve the answer