Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi again Salim,
Here we go:
The trick is to activate Total and then use dimensionalty().
What is dimensionality?
Suppose we have 3 dimensions, the first 1 is dimensionality()=1, second dimensionality()=2 and so on
As for the total: It's dimensionality()=0
So, what we do is:
1) activate the total in our dimension now (country &' :'&product) , the & is to concatenate different fields, expressions, strings..
2)alter our expression:
from :if(Aggr( sum([Market Share]),product)<2,sum([Market Share]),null())
to :
if(Dimensionality()=0,Avg({1} total [Market Share]),
if(Aggr( sum([Market Share]),product)<2,sum([Market Share]),null()))
The {1} means give me the Avg market share no matter the selections we make.
total means it's independant of the dimension, it won't be divided by the dimension (no aggregation will be made, it will always show the same thing no matter the dimension it's divided by..)
The expressions means, if dimensionality()=0 (total), then give me the avg fort the all market share, else, give my expression.
Result:
See the attached app
Hope that answers your question?
Thank you for your answer, but here your merge cells of column but i want merge of row of one column or put the same value in each row of this column.
I allow to ask you a second question.
I have this pivot table :
, but I want calculated ratio in each line a ratio like this :
For example to BA I want the value of the following calculation 51,.../43577,..
Do you have a idea ?
Like this?
sum([Market Share])/ sum(total [Market Share])
Please mark helpful the answers that have been helpful to you (under action, buttom left)
What's the point of repeating the same value over and over again while you can have the information only once with other informations in the same time?
You simply add another measure:
avg({1} total Market)
See the attached app:
And don't forget to mark the correct answer as so if your questions have been answered !
Hope this helps?
Yes i will mark the correct answer when i obtain the correct answer.
In first i want merge but if i can't i want the same value in each row for one country.
Indeed, the first measure show th market share of one product in the country. The second measure is the ratio of all market by population of country (it's why for one country it's the same value).
Then, when i use this expression "sum({<nom_Mesure={exp}>} total [valeur])", if I don't select a country (equals nom_Du_Pays) i don't have the good value :
if don't select value of country (equals nom_Du_Pays)
if I select Allemagne value
Do you have an explain ?
I'm sorry for this questions...
Good morning Salim, And hey, you don't have to be sorry for these questions ! You're not bothering me at all ! 
Be my guest 
You can always add more measures in a table.
In your case, you can have 2 measures.
See the example below:
I want have a measure that returns the Avg market share by country product(so it depends on the dimensions)
and a measure that always shows the Total Avg market share:
So
we choose a table:
As dimensions:
Country
Product
As measures:
1) Avg([Market Share])
2) Avg( TOTAL [Market Share])
Result:
When I select a country, I will always have good results:
