Exclude null values and calculate Sales %

[devarasu07]

Hi All,

Please refer the attachment, I've used below expression and trying to achieve attached expected output screen (i.e exclude null from the country and calculate Sales %). please advise me. Thanks

Dimension:

Country

=if(WildMatch(Country,'SAUDI ARABIA','INDONESIA','UAE','THAILAND','MALAYSIA','VIETNAM','SOUTH KOREA','INDIA','PHILIPPINES','MACAU','CAMBODIA'),Country)

Measure:

Sales=Sum(TotalNetprice)

Sales %= Sum(TotalNetprice)/ Sum(TOTAL TotalNetprice)

[marcus_sommer]

Try it with hide NULL by your dimension.

- Marcus

[sunny_talwar]

Basically check the 'Suppress When Value is Null' on the dimension tab

[devarasu07]

Hi Marcus,

Thanks for your reply. I have tried doing that method. But the Sales % still takes into account the NULL values. Any idea on how to apply where clause or any other logic in the sales % expression? [Attached the sample data and document]

Thanks

[sunny_talwar]

Try using set analysis instead of calculated dimension

[sunny_talwar]

Or if you have no issues in using a straight table instead of a pivot table, you can simplify your second expression by using 'relative' check mark for your second expression and use Column(1) as expression

[antoniotiman]

Maybe

sum(Sales)/RangeSum(Top(sum(Sales),1,NoOfRows()))

Regards,

Antonio