topic Re: SPD share Calculation in DAX Commands and Tips

SPD share Calculation

Anonymous — Wed, 02 Feb 2022 12:52:06 GMT

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 .

Re: SPD share Calculation

amitchandak — Wed, 02 Feb 2022 11:55:51 GMT

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 )

Re: SPD share Calculation

Anonymous — Wed, 02 Feb 2022 12:56:25 GMT

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

Re: SPD share Calculation

Anonymous — Wed, 02 Feb 2022 13:02:09 GMT

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

Re: SPD share Calculation

amitchandak — Wed, 02 Feb 2022 13:14:45 GMT

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)

Re: SPD share Calculation

Anonymous — Thu, 03 Feb 2022 06:14:22 GMT

amitchandak let me try and will update now sir