Forum Discussion
Share based on total for specific value
Hello,
I have the following matrix:
I have a column in my table named "Exists" which has value "1" if a brand exists in a store. I want to calculte the Share for each store compared to the Country. So for store 1 I would like to have the Share of 93,1%. I created three measures:
- Total Exists which is sum(Table[Exists])
- Exists Country: CALCULATE(sumx(Table,Table[Exists]),Table[MARKET]="Country")
- Share Exists: DIVIDE([Total Exists],[Exists Country],0)
But something is not working. Could you, please, help me?
Hi Anonymous
Create measures
total exsits = SUM('Table'[exsits]) exsits in country = CALCULATE(SUM('Table'[exsits]),FILTER(ALLSELECTED('Table'),'Table'[market]="country")) % = [total exsits]/[exsits in country]Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous , Not very clear. But seem like you need % of sub total
https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
Hi amitchandak ,
Thank you for your reply. Sorry if my question was not clear enough. I need to have the value for 'Exists Country' on each row of the matrix in order to calculate the share. So in the area marked with red I want to have the value 29 on each row.
Anonymous ,Can you share sample raw data in table format
Hi Anonymous
Create measures
total exsits = SUM('Table'[exsits]) exsits in country = CALCULATE(SUM('Table'[exsits]),FILTER(ALLSELECTED('Table'),'Table'[market]="country")) % = [total exsits]/[exsits in country]Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi v-juanli-msft ,
Thank you, it worked!
I have created a measure like this, but the total for country was not on each row. Maybe because I was not using allselected. I'm new to this calculations, I don't now exactly what to use and when.
But now it works, thank you!
