6 Replies Latest reply on May 23, 2016 1:50 PM by Katrina Roque

# Need help with Calculating rate for dimensions

Hello,

I am wondering if it is possible to calculate the rate of specific dimensions. I basically need to calculate the rate of certain codes in my data and the calculation I'm trying to accomplish is sum(code1)/Sum(all codes)

The problem I'm having is that the codes I have are listed in rows under 1 column vs different columns for each code.

I've attached my workbook for reference.

Thank you so much for your help!

• ###### 1. Re: Need help with Calculating rate for dimensions

Hi Katrina,

You can accomplish sum(code1)/Sum(all codes) pretty easily with LOD expressions.

First, create an LOD that gives you the total for all codes: {Fixed : SUM([Count Of Codes])}

Second, create an LOD that gives you the total for each code: {Fixed [code] : SUM([Count Of Codes])}

Third, create your ratio of code/all codes:  [Codes by Code]/[Codes Total]

Use the third field you created as your measure.

Good luck,

Ivan

1 of 1 people found this helpful
• ###### 2. Re: Need help with Calculating rate for dimensions

Hi Ivan,

Thank you so much for replying to my post!

If I added another column, how would the LOD change?  For example, I added an ID column and I would like to filter the codes per ID column.  I see that if I add the ID column and filter on that ID the ratio changes.

• ###### 3. Re: Need help with Calculating rate for dimensions

Hi Kristina,

If you remove the customer filter then I would expect to see 100%.  I don't see an ID column in the dataset and I'm not clear on how you are using it in filters or columns and rows.

• ###### 4. Re: Need help with Calculating rate for dimensions

Hi Ivan,

Sorry for being unclear.  I'd like to filter by a specific ID and am expecting that the count of codes for the specified ID to equal 100%.  Then from there I'd the to get the ratio of each code within each specified ID.

In the image below the ratio I got is .6960.  I've attached an updated workbook and am using the tab called "ratio of codes"

I appreciate your help on this Ivan!

• ###### 5. Re: Need help with Calculating rate for dimensions

Hi Katrina, You can elevate you ID filter to context and that will give you 100%.  It shows .6960 when id1 is selected because type1 and type2 are the only types associated with that id.

1 of 1 people found this helpful
• ###### 6. Re: Need help with Calculating rate for dimensions

Thank you SO MUCH for your help Ivan!