Forum Discussion

Arjun_reddy's avatar
Arjun_reddy
Helper I
3 years ago
Solved

dax query

Hi Community team,

 

need you help on one of my requirement:
can you please write a dax measure on the below formula 
formula=((max year of total occasions on highest retail channel)-(same period last year of total occasions on retail channel))/(same period last year of total occasions on retail channel)
note: which is havin highest retail channel on max year for the same retail channel should reflect for pevious year 

categoryyearsales Row Labelsbulbschairssofatables 
chairs20215000 2021400013800550012000 
tables20216000 20228000800092003400 
bulbs20226000       
sofa20224500  sofa is the highest value in max year the value is: 9200 and the same category sofa get the value on previous year sales    
chairs20228000       
chairs20216000 growth rate:67%    
chairs20212800 sample formula=( (max year of highest top1 category on sales)-(previous year of same category on sales))/(previous year of same category on sales) 
sofa20224700       
bulbs20222000       
sofa20215500       
tables20216000       
tables20223400       
bulbs20214000       

Thanks,

Arjun Reddy. L

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    maybe try to convert year column from TEXT to number

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    Arjun_reddy 

    is this what you want?

    Measure = 
VAR  _year=max('Table'[year])
VAR _t=TOPN(1,SUMMARIZE(FILTER('Table','Table'[year]=_year),'Table'[category],"sale",sum('Table'[sales])),[sale])
var _C= maxx(_t,[category])
VAR _cy=sumx(FILTER('Table','Table'[year]=_year&&'Table'[category]=_C),'Table'[sales])
VAR _py=sumx(FILTER('Table','Table'[year]=(_year-1)&&'Table'[category]=_C),'Table'[sales])
return _cy/_py-1

    pls see the attachment below

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    What if more than 1 category has the highest sale in the farthest year. say sofa and bulbs?  How should the growth rate be calculated then?

  • Arjun_reddy's avatar
    Arjun_reddy
    Helper I
    3 years ago

    Hi ryan,

     

    I am getting the value format error can you please correct the below query, and where i can add the power bi file into this post, can you please help me.

    growthrate_on_occ =
    VAR  _year=max('Tostitos_MP'[YEAR])
    VAR _t=TOPN(1,SUMMARIZE(FILTER('Tostitos_MP','Tostitos_MP'[YEAR]=_year),'Tostitos_MP'[RETAIL CHANNEL],"sale",sum('Tostitos_MP'[TOTAL OCCASIONS])),[sale] )
    var _C= maxx(_t,[RETAIL CHANNEL])
    VAR _cy=sumx(FILTER('Tostitos_MP','Tostitos_MP'[YEAR]=_year&&'Tostitos_MP'[RETAIL CHANNEL]=_C),'Tostitos_MP'[TOTAL OCCASIONS])
    VAR _py=sumx(FILTER('Tostitos_MP','Tostitos_MP'[YEAR]=(_year-1)&&'Tostitos_MP'[RETAIL CHANNEL]=_C),'Tostitos_MP'[TOTAL OCCASIONS])
    return _cy
     
    Thanks, 
    Arjun Reddy
    • ryan_mayu's avatar
      ryan_mayu
      Super User
      3 years ago

      maybe try to convert year column from TEXT to number

      • Arjun_reddy's avatar
        Arjun_reddy
        Helper I
        3 years ago

        after changed the year format as value, the dax query working fine and getting the accurate value,

        Thanks a lot team, your helping is really awesome 

         

        Arjun Reddy.