Forum Discussion
% Of Row Total
Hello,
I have created a matrix in the following format
jan feb mar apr may june etc etc
Product 1 3 10
Product 2 4 10
Product 3 5 12
I want to change the values to show as a percentage of the coloum total , eg. product 1 jan is x % of jan's figure etc .
Has anyone manage to acheive this ?
Kind Regards
Thomas
Hi thomaskelly,
I want to change the values to show as a percentage of the coloum total , eg. product 1 jan is x % of jan's figure etc .
In this scenario, you should use ALLEXCEPT function to create the new measure. Assume the "item description", "year and month", "sales" columns all in the same table called "Product Sales", then the formula for the new measure should like below.
% of column total = DIVIDE ( SUM ( [sales] ), CALCULATE ( SUM ( [sales] ), ALLEXCEPT ( 'Product Sales', [year and month] ) ) )Regards
6 Replies
thomaskelly - you could create a new measure with a formula like this:
% of all Products = DIVIDE( [Your Measure]; CALCULATE( [Your Measure]; ALL( Product ) ) )
Replace [Your Measure] with the name of your current measure and Product with the name of the dimension table that holds your products.
Thanks for the reply,
I dont have any measures set up just pulling sales data for each product through directly from an excel document.
In my matrix I have
rows= item description
columns = year and month
values = sales
Then create a measure ;-)
Hi thomaskelly,
I want to change the values to show as a percentage of the coloum total , eg. product 1 jan is x % of jan's figure etc .
In this scenario, you should use ALLEXCEPT function to create the new measure. Assume the "item description", "year and month", "sales" columns all in the same table called "Product Sales", then the formula for the new measure should like below.
% of column total = DIVIDE ( SUM ( [sales] ), CALCULATE ( SUM ( [sales] ), ALLEXCEPT ( 'Product Sales', [year and month] ) ) )Regards
