Forum Discussion
SPD share Calculation
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 ,
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)
5 Replies
- amitchandak
Super User
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 ) 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
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)
amitchandak let me try and will update now sir
amitchandak sir i am getting Failed to resolve name 'SELECTEDVALUE'. It is not a valid table, variable, or function name error