SPD share Calculation

[Anonymous]

 i have table name called Master DB .  My table  data looks like the below .

Category Dec             Market name            Actuals

 Cigarettes                    AZ                              500

 Cigarettes                    AU                              300

 Cigarettes                   ZM                              200

      RRP                         AZ                              600

      RRP                        AU                              100

      RRP                       ZM                                350

I want calcualte SPD share  for individual  Market name total of the  individual   Category Dec   actuals

if suppose  going to calculate spd share for  AZ  for  Cigarettes  category .

Cigarettes  totalactuals =1000     

AZ  value for  Category = 500

my calculation should be like

 SPD share = ( selected Category Dec   total - selected marketname value) / selected Category Dec   total

  Example :  SPD share =(1000-500)/ 1000

i have created formula below 

SPD SHARE =

VAR selectedSPD =
SELECTEDVALUE ( 'Master DB'[Category Dec] )
VAR totalsum =
CALCULATE ( SUM ( 'Master DB'[Actuals] ), ALL ( 'Master DB' ) )
VAR selected_spddesc_value =
CALCULATE ( SUM ( 'Master DB'[Actuals] ), 'Master DB'[Category Dec] = selectedSPD )
RETURN
DIVIDE ( ( selected_spddesc_value - totalsum ), selected_spddesc_value )

But its taking whole actuals value . i want selected actuals value for the calcualtion.

thanks in advance .

[Anonymous]

@amitchandak  sir i am getting Failed to resolve name 'SELECTEDVALUE'. It is not a valid table, variable, or function name error  

[Anonymous]

@amitchandak  sir  Not able get that 1000 -500/1000   
For example 1000  -  this is  total value for selected  Cigarettes category desc  .

                     500 - selected market value .

 let me use above  your mentiond measure  sir  

[amitchandak · Accepted solution]

@Anonymous ,

That will be

New measure =

var _cat = CALCULATE ( SUM ( 'Master DB'[Actuals] ), 'Master DB'[Category Dec] = max( 'Master DB'[Category Dec] ) )

var _self = SUM ( 'Master DB'[Actuals] )

return

divide(_cat -_self,_cat)

[Anonymous]

@amitchandak  let me try  and will update now sir 

[amitchandak]

@Anonymous , Not able get that 100 -500/100

but need be like

SPD SHARE =

VAR selectedSPD =
SELECTEDVALUE
VAR totalsum =
CALCULATE ( SUM ( 'Master DB'[Actuals] ), ALL ( 'Master DB' ) )
VAR _select_cat =
CALCULATE ( SUM ( 'Master DB'[Actuals] ), 'Master DB'[Category Dec] = max( 'Master DB'[Category Dec] ) )
VAR _select_mar =
CALCULATE ( SUM ( 'Master DB'[Actuals] ), 'Master DB'[ Market name] = max( 'Master DB'[ Market name] ) )
RETURN
DIVIDE ( ( _select_cat - _select_mar ), totalsum )