I am calculating percentage of product values as below.
Prod % Value
But when I do Top 5 or exclude other, the percentage values shouldn't change, not sure how to get it done.
%Value is calculated as sum(value)/Total(Value)
Solved! Go to Solution.
Can you elaborate a bit more?
Is what you're showing a matrix with Prod on rows and %Value (measure) in values?
What do you mean exactly by "when I do Top 5 or exclude other" ?
You can acheive this by using ALL() in your measure.
It will be easier to help if you can show some sample data table from which you are creating this report.
It is better to explain the issue like below example.
"My problem is to get the id number separated from a column which shows the data as below."
My actual data is in a column as below
The reuslt I need should be
This way you can explanin the issue clearly and instead of pasting the screenshots in the question please consider copying the excel table.
I hope this will lead solving the issue fast.
The formula works well, but when I do Top 2 records my percentage values are changing. but in the example it works well, but in real data is getting changed a lot. not sure why??
You should use SUMX and Values.
Try the following formula:
Percentage = SUMX ( VALUES( Table[Prod] ), CALCULATE ( SUM ( Table[value] ) ) ) / SUMX ( VALUES( Table[Prod] ), CALCULATE ( SUM ( Table[Value] ), All(Table[Prod]), FILTER('Table' , Table[Prod] <> "D") ) )