% of total with two dimensions

[Fabien]

Hi there,

I managed to create an expression in which the Treemap shows the % of 'Picing Group' per 'Quote Type'.

The expression works well as long as i don't select a Pricing Group. As soon as I do select one, the % goes up to 100% for all Quote Types, which should not be the case.

Appreciate any assistance in getting this expression to display the correct % when a Pricing Group is selected.

=If(GetSelectedCount([Week Num])=0,
sum({<[Week Num]={$(=Week(Today())-1)}>} TEU)/sum({<[Week Num]={$(=Week(Today())-1)}>} total <[Quote Type]> TEU)
,Sum(TEU)/Sum(total <[Quote Type]> TEU))

[Fabien]

Help anyone? 😞

[Channa]

you need to exclude selection

=sum ( total {<Pricing Group= >} TEU)

[sunny_talwar · Accepted solution]

To be precise, try this

=If(GetSelectedCount([Week Num]) = 0,

  Sum({<[Week Num] = {$(=Week(Today())-1)}>} TEU)
  /
  Sum({<[Week Num] ={$(=Week(Today())-1)}, [Pricing Groups]>} TOTAL <[Quote Type]> TEU),

  Sum(TEU)
  /
  Sum({<[Pricing Groups]>}TOTAL <[Quote Type]> TEU)

)

[Fabien]

Thank you both for your responses.

It works great!! 🙂