June 15, 2021 at 1:06 pm
Hello. Iām starting in the ssrs, using sql 2019
I want calculate the percentage of a column in matrix, but the percentage should be not over the total which is easy, but over the main row, in this case Product.
In the following example, the column qty shows the quantity of the product1 in each storage, and the column qty_P shows percentage of the value over the total of product1, not over the total of the products.
Product___storage____qty__qty_P
-----------+-----------+-------+-----------+
Product1----Magasin1----5------10%
-----------+-----------+-------+-----------+
--------------Magasin2----20----40%
-----------+-----------+-------+-----------+
--------------Magasin3----25----50%
-----------+-----------+-------+-----------+
Product2---Magasin1----15-----30%
-----------+-----------+-------+-----------+
--------------Magasin2----25----50%
-----------+-----------+-------+-----------+
--------------Magasin3----10----20%
-----------+-----------+-------+-----------+
To calculate the qty_p tried this and many others, but got wrong values (i know how to turn it into % just would like to know the calculation)
=Count(Fields!qty_p.Value ) / Count(Fields!qty_p.Value, "Product" )
If you could help I would really appreciate
Thanks
June 15, 2021 at 1:48 pm
Sum(Fields!qty.Value) / Sum(Fields!qty.Value,"Product") should give you percentage of product qty
Sum(Fields!qty.Value) / Sum(Fields!qty.Value,"DataSet1") should give you percentage of total qty
Are Product and storage groups, is qty a sum of qty?
Far away is close at hand in the images of elsewhere.
Anon.
June 15, 2021 at 9:02 pm
Hi David, that's perfect, applied this logic to other reports also and fine !!. I see now the logic of percentages across the diferent levels of rows and columns, a really common issue I see.
Sorry still cannot help others in the forum, just starting in ssrs, but will keep trying !
Thanks
June 16, 2021 at 8:09 am
Hi David, that's perfect, applied this logic to other reports also and fine !!. I see now the logic of percentages across the diferent levels of rows and columns, a really common issue I see.
Sorry still cannot help others in the forum, just starting in ssrs, but will keep trying !
Thanks
You're welcome and thanks for the feedback.
Don't worry about the helping part, sooner or later you will be able to help someone, only a matter of time. š
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply